Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    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)?

  2. #2
    Plutonium Lounger
    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.

  3. #3
    5 Star Lounger
    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 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. #4
    Plutonium Lounger
    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.

  5. #5
    5 Star Lounger
    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.

  6. #6
    5 Star Lounger
    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 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. #7
    WS Lounge VIP sdckapr's Avatar
    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

  8. #8
    5 Star Lounger
    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!

Posting Permissions

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