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
you can do something like this…
assuming you have one table with the data in a single column.
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
You could also use the INDEX function, which is a bit more succinct and in theory less "volatile" (therefore, faster) in a large table.
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
How to get data from one column into multiple?
