Results 1 to 8 of 8

20050301, 22:47 #1
 Join Date
 Jan 2001
 Location
 Ephrata, Washington, USA
 Posts
 683
 Thanks
 0
 Thanked 0 Times in 0 Posts
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)?

20050301, 23:01 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
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.

20050302, 01:46 #3
 Join Date
 Jan 2001
 Location
 Ephrata, Washington, USA
 Posts
 683
 Thanks
 0
 Thanked 0 Times in 0 Posts
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 A1FX1, 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!

20050302, 06:53 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
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.

20050302, 19:43 #5
 Join Date
 Jan 2001
 Location
 Ephrata, Washington, USA
 Posts
 683
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20050302, 19:56 #6
 Join Date
 Jan 2001
 Location
 Ephrata, Washington, USA
 Posts
 683
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: separate and group numbers (2003)
Ok. I tried redoing 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.

20050302, 20:18 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: separate and group numbers (2003)
Your starting row is row 7 and you have 12 columns (7*12 = 84)
Steve

20050302, 20:18 #8
 Join Date
 Jan 2001
 Location
 Ephrata, Washington, USA
 Posts
 683
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: separate and group numbers (2003)
Thank you! This has been so fun. Thank you both!