Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts

    2d grid into single column (Excel 97 -SR2)

    What I would like to do is to be able to turn a 2 dimensional grid layout on one worksheet into a single column of data on another.

    I have attached a copy of what I would like to see, but I want to have a reliable means of converting the layout regardless of the dimensions of the original source table.

    I know someone out there will have a solution. - if its trivially easy then I apologize in advance

  2. #2
    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: 2d grid into single column (Excel 97 -SR2)

    Try this:
    =INDEX(DATALIST,MOD(ROW()-1,COLUMNS(DATALIST))+1,INT((ROW()-1)/COLUMNS(DATALIST))+1)

    Where DATALIST is a named range for your dataset. NOTE: if you don't start in row1 you will have to adjust the formula!

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: 2d grid into single column (Excel 97 -SR2)

    That's great - but I found I had to reverse the row and column references like this
    =INDEX(DATALIST,INT((ROW()-1)/COLUMNS(DATALIST))+1,MOD(ROW()-1,COLUMNS(DATALIST))+1)

    Do you agree?

    Many thanks for pointing me in the right direction - I've not come across INDEX before.

  4. #4
    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: 2d grid into single column (Excel 97 -SR2)

    Your example sheet goes down col A: A1, A2, A3, A4 then does col B, B1, B2, B3, B4 then C, then COl D. that is what my formula does.

    Your formula goes ACROSS the columns:
    Row 1: A1, B1, C1, D1, then
    Row 2: A2, B2, C2, D2, then
    Etc
    If that is what you want then your formula is correct.

    But if you want the display to be like your example worksheet then I stand by my post.

    Steve
    Here is the "source data"

    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center valign=bottom>1</td><td valign=bottom>a100</td><td valign=bottom>b100</td><td valign=bottom>c100</td><td valign=bottom>d100</td><td align=center valign=bottom>2</td><td valign=bottom>a200</td><td valign=bottom>b200</td><td valign=bottom>c200</td><td valign=bottom>d200</td><td align=center valign=bottom>3</td><td valign=bottom>a300</td><td valign=bottom>b300</td><td valign=bottom>c300</td><td valign=bottom>d300</td><td align=center valign=bottom>4</td><td valign=bottom>a400</td><td valign=bottom>b400</td><td valign=bottom>c400</td><td valign=bottom>d400</td></table>

    Col A shows your "single Column"
    Col B is : My proposal: =INDEX(DataList,MOD(ROW()-1,COLUMNS(DataList))+1,INT((ROW()-1)/COLUMNS(DataList))+1)
    Col C is : Your proposal: =INDEX(DataList,INT((ROW()-1)/COLUMNS(DataList))+1,MOD(ROW()-1,COLUMNS(DataList))+1)

    Use whichever result you want.

    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center valign=bottom>1</td><td valign=bottom>a100</td><td valign=bottom>a100</td><td valign=bottom>a100</td><td align=center valign=bottom>2</td><td valign=bottom>a200</td><td valign=bottom>a200</td><td valign=bottom>b100</td><td align=center valign=bottom>3</td><td valign=bottom>a300</td><td valign=bottom>a300</td><td valign=bottom>c100</td><td align=center valign=bottom>4</td><td valign=bottom>a400</td><td valign=bottom>a400</td><td valign=bottom>d100</td><td align=center valign=bottom>5</td><td valign=bottom>b100</td><td valign=bottom>b100</td><td valign=bottom>a200</td><td align=center valign=bottom>6</td><td valign=bottom>b200</td><td valign=bottom>b200</td><td valign=bottom>b200</td><td align=center valign=bottom>7</td><td valign=bottom>b300</td><td valign=bottom>b300</td><td valign=bottom>c200</td><td align=center valign=bottom>8</td><td valign=bottom>b400</td><td valign=bottom>b400</td><td valign=bottom>d200</td><td align=center valign=bottom>9</td><td valign=bottom>c100</td><td valign=bottom>c100</td><td valign=bottom>a300</td><td align=center valign=bottom>10</td><td valign=bottom>c200</td><td valign=bottom>c200</td><td valign=bottom>b300</td><td align=center valign=bottom>11</td><td valign=bottom>c300</td><td valign=bottom>c300</td><td valign=bottom>c300</td><td align=center valign=bottom>12</td><td valign=bottom>c400</td><td valign=bottom>c400</td><td valign=bottom>d300</td><td align=center valign=bottom>13</td><td valign=bottom>d100</td><td valign=bottom>d100</td><td valign=bottom>a400</td><td align=center valign=bottom>14</td><td valign=bottom>d200</td><td valign=bottom>d200</td><td valign=bottom>b400</td><td align=center valign=bottom>15</td><td valign=bottom>d300</td><td valign=bottom>d300</td><td valign=bottom>c400</td><td align=center valign=bottom>16</td><td valign=bottom>d400</td><td valign=bottom>d400</td><td valign=bottom>d400</td></table>

  5. #5
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: 2d grid into single column (Excel 97 -SR2)

    To be honest I hadn't checked whether it was doing row/column or column/row, that closely. Thanks for pointing it out.
    My bigger problem with the original formula was that when I used a 7x4 grid I was getting
    a100
    a200
    a300
    a400
    #REF!
    #REF!
    #REF!
    b100
    b200
    b300
    b400
    #REF!
    #REF!
    #REF!
    c100
    c200

    etc.

  6. #6
    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: 2d grid into single column (Excel 97 -SR2)

    Sorry, I got my rows and columns mixed up (I knew I should hve worked with a range that was NOT symmetrical!)

    Change the columns() to rows():

    =INDEX(datalist,MOD(ROW()-1,ROWS(datalist))+1,INT((ROW()-1)/ROWS(datalist))+1)

    Steve

  7. #7
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: 2d grid into single column (Excel 97 -SR2)

    Many thanks
    I'm using this a lot already.

Posting Permissions

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