Convert long single column into multiple columns

Question

Level 1

9 points

Convert long single column into multiple columns

Hello all, I have a sheet with one table, the table has a single column. No headers. The column is filled with first names from A-Z. The column is roughly 4,500 rows long. Sorting this single column is obvious and easy. My problem comes down to printing. Printing this spreadsheet is very wasteful and takes about 150 sheets.

I would like to convert the single column into maybe at least 5 columns to fill the page, and have the names continue from column one alphabetically to column two etc... Top to bottom left to right from column to column in kind of a serpentine pattern. Ideally if the sorting could be preserved, in case I add more names, that'd be perfect. Thanks for any help!

iMac, Mac OS X (10.6.5)

Posted on Jan 3, 2011 12:57 PM

Reply

Answer 1

Barry

Level 9

60,023 points

Jan 3, 2011 2:20 PM in response to RyLo

Hi RyLo,

Welcome to Apple Discussions and the Numbers '09 forum.

Click on a cell in your table and sort the list using the Reorganize button.
Press command-A to select all.
Copy.
Open a blank Pages word processing template.
Click on the body of the blank document and Paste.
The table containing the list will be pasted into the document, spanning several pages.
Go to Format > Table > Convert Table to Text.
The table will be converted to a list.
Open the Inspector and choose the layout Inspector.
Set the number of columns to the number you want.

To update the list, you can either enter new names into their proper position in the list in Pages, or add them to the single column table in Numbers, sort that table and repeat the process above to move the list to Pages and arrange it in columns.

Regards,
Barry

Reply

Answer 2

RyLo Author

Level 1

9 points

Jan 3, 2011 1:25 PM in response to RyLo

A post by badunit is good enough, although it reads left to right, then top to bottom. I would like it to be top to bottom then left to right, like a newspaper. but oh well! Thanks badunit

Re: alphabetizing lists in numbers '09
Posted: Jul 13, 2010 11:49 AM in response to: Marc Eaton1

I'm not sure how your original table was arranged. If it was alphabetized across a row then continued to the following rows like this

Bob Charles James
John Kathy Mary

you can enter the names in a single-column table where you can make all your additions, deletions, and edits and re-alphabetize and then use a formula in a different table for display purposes that will automatically arrange the names in the way you choose. This formula will make a table arranged as above:

=IFERROR(IF(ISBLANK(INDIRECT(ADDRESS(COLUMN()+(COLUMNS(2:2)-1)*(ROW()-2),2,,,"Ta ble 2"))),"",INDIRECT(ADDRESS(COLUMN()+(COLUMNS(2:2)-1)*(ROW()-2),2,,,"Table 2"))),"")

It assumes the two tables each have a header column and header row and it based on your alphabetized names being listed in column B of Table 2. Put it in cell B2 of the other table then copy/paste it to all the other cells. It isn't really as complex as it looks, the error checking and checking for blanks is what makes it look formidable.

If you want your table arranged a different way, a similar formula could be made.

Reply

Answer 3

KOENIG Yvan

Level 8

41,819 points

Jan 3, 2011 1:40 PM in response to RyLo

I posted a formula creating the wanted table with your wished structure. Alas, I'm too tired to search for you.
You may search with a keystring like

yvan AND indirect
or
yvan AND offset

Yvan KOENIG (VALLAURIS, France) lundi 3 janvier 2011 22:40:11

Reply

Answer 4

Badunit

Level 7

30,714 points

Jan 3, 2011 2:30 PM in response to RyLo

If your names start in Table 1 cell B2 and you want your printing table (Table 2) to also start in cell B2 and if you want 25 names per column then

Table 2 cell B2 =IFERROR(OFFSET(Table 1 :: $B$2,ROW()-2+(COLUMN()-2)*25,0),"")
Fill this down so there are 25 rows of it total then fill across.
Change the 25 to another number if you want more names in each column.

This formula does not ensure you got everyone or have not duplicated someone. You could miss people by not filling down enough rows or not having enough columns. You could duplicate people by filling down too many rows.

This formula also does not check if you've hit the end of your list, in which case you will get 0's instead of names. Th 'end of list' problem is solved with

=IFERROR(IF(ISBLANK(OFFSET(Table 1 :: $B$2,ROW()-2+(COLUMN()-2)*25,0)),"",OFFSET(Table 1 :: $B$2,ROW()-2+(COLUMN()-2)*25,0)),"")

As you can see, it gets longer very fast as you add checks.

And Barry is taking all the fun away from designing formulas so don't listen to him 🙂

Message was edited by: Badunit

Reply