Thread: separate and group numbers (2003)

1. separate and group numbers (2003)

I have 180 numbers in a straight horizontal line representing monthly total for 15 years (15 years x 12 months per year).

I want to copy this data into 15 lines with 12 numbers per line.

From:
12342341234343454564654656756776878898989098098089 795785

To:
123423412343434
545646546567567
768788989890980

What is the easiest way to do this (I am code illiterate, so other options would be appreciated)?

2. Re: separate and group numbers (2003)

You can use the INDEX worksheet function - see attached workbook. In the worksheet, the original data are in A1:FX1. The formula in A7 is

=INDEX(\$A\$1:\$FX\$1,1,12*ROW()+COLUMN()-84)

and this is filled right to L7, then down to A21:L21. If you want to start in another row, adjust the -84 to -12*startrow.

3. Re: separate and group numbers (2003)

Ok... well that's a complicated function. I will study it more so I understand just what you did. It does work the way I want it to, but I don't know what the column()-84 means. I read the explanation for this function in my Excel book, but I don't understand the last part of your formula.

From what I've read, your formula says to look at A1-FX1, row 1, column 1 and return the first 12 cells in that row.... right? I understand if you don't have time to actually teach me the index function. I'll read more in the book. This looks very interesting! Thank you!

4. Re: separate and group numbers (2003)

=INDEX(\$A\$1:\$FX\$1,1,12*ROW()+COLUMN()-84)
or, since \$A\$1:\$FX\$1 has only one row, this is equivalent:
=INDEX(\$A\$1:\$FX\$1,12*ROW()+COLUMN()-84)

The INDEX function looks up the (12*ROW()+COLUMN()-84)th cell in the range \$A\$1:\$FX\$1.

The ROW() function returns the row number of the cell containing the formula.
The COLUMN() function returns the column number of the cell containing the formula.
Let's take cell C8 as an example. For this cell, ROW() = 8 and COLUMN() = 3, so 12*ROW()+COLUMN() evaluates to 12*8+3 = 99.
C8 is the 3rd cell in the 2nd row of our target range, so we want C8 to return the 15th value (first row values 1...12, second row values 13...24, etc.). We subtract 84 from 12*ROW()+COLUMN() = 99 to get the correct index number.

If we had started the target range in another row, we would have had to subtract a different number to get it right.

5. Re: separate and group numbers (2003)

Thank you Hans. That makes sense now. This is so neat! I can think of several people at work who can use this.

6. Re: separate and group numbers (2003)

Ok. I tried re-doing this using our numbers and I still don't get why you used -84. Where does the 84 come from? Why not some other number? I am SOOOO close to understanding this and using it in another spreadsheet.

7. Re: separate and group numbers (2003)

Your starting row is row 7 and you have 12 columns (7*12 = 84)

Steve

8. Re: separate and group numbers (2003)

Thank you! This has been so fun. Thank you both!

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•