BenBarclay

Q: How to get data from one column into multiple?

I have a numbers spreadsheet with 9,000 items of data, all in one column.

 

Is it possible to automatically get numbers to spread this data into multiple columns, so I can fit more data onto an A4 page? Or do I need to manually cut and paste them?

MacBook Air, OS X Yosemite (10.10.2), Trash

Posted on Sep 16, 2016 6:18 PM

Close

Q: How to get data from one column into multiple?

  • All replies
  • Helpful answers

  • by Wayne Contello,Apple recommended

    Wayne Contello Wayne Contello Sep 16, 2016 7:39 PM in response to BenBarclay
    Level 6 (19,357 points)
    iWork
    Sep 16, 2016 7:39 PM in response to BenBarclay

    you can do something like this…

     

    assuming you have one table with the data in a single column.

     

    Screen Shot 2016-09-16 at 9.36.19 PM.png

     

    The original, single-column data is in the table on the left titled "source data".

     

    the table on the right retrieves the data:

    In the table "Condensed Data" on the right:

    A1=IFERROR(OFFSET(source data::$A$1, ROWS(A:D)×(COLUMN()−1)+ROW()−1, 0), "No more data")

    this is shorthand for... select cell A1, then type (or copy and paste from here) the formula:

    =IFERROR(OFFSET(source data::$A$1, ROWS(A:D)×(COLUMN()−1)+ROW()−1, 0), "No more data")

     

    select cell A1, copy

    select all cells in the table, paste

     

    now expand the table as needed

  • by SGIII,Apple recommended

    SGIII SGIII Sep 16, 2016 8:30 PM in response to BenBarclay
    Level 6 (10,782 points)
    Mac OS X
    Sep 16, 2016 8:30 PM in response to BenBarclay

    You could also use the INDEX function, which is a bit more succinct and in theory less "volatile" (therefore, faster) in a large table.

     

    Screen Shot 2016-09-16 at 11.26.18 PM.png

     

    The formula in A1, copied right and down, is:

     

      =IFERROR(INDEX(source data::$A,ROW()+ROWS($A)*(COLUMN()−1)),"")

     

    This assumes no blank rows at the bottom of your single-column source data table.

     

    SG