Numbers — One long column to several columns

Hi colorogall,

"What am I missing?"

That "Columns" on a spreadsheet table are not the same as "Columns" in the layout of a word processor page.

In a word processor document, columns are open ended, and if placed on the same page, allow text to flow from one column to the next on the same page, and to flow from the bottom of the last column on a age to the top of the first column on the next page. Pressing the return key inserts a paragraph break character, forcing the text that follows down to the next line, but still in the same column (until there in no more space in that column, and the following text moves into a new column.)

In a Spreadsheet table, each column contains one or more cells. Pressing return after each single word confirms the entry of that word in that cell AND moves the insertion point into the next cell in that column. After 400 words, each followed by a return, you have a column with (at least) 400 cells, each containing a single word.

One way to move them is to Select, Copy and paste each 'one page' group of words from the original column, into a new column added to the original table. When done, Select all the rows from which the original words have been copied into new rows, then delete those rows.

Considering that this includes several several repetitive steps, the is lots of opportunity for mishaps ending in lost data. The best way to avoid that is to work on a COPY of your original document.

Another method is to use a formula to copy the single words from the one column table to a new multiple column table. Here's an example used to answer a similar question a few days ago:

The long and narrow table shown on the left contains the original 400 word list, sorted into alphabetical order before being transferred to the shorter but wider table to the right.

The formula doing the transfers was entered as shown in cell A2 of the wider table, then filled down to row 26 and right to column D.

The same formula will work in your case, but will require one (or two) minor changes to fit the number of rows in your destination table. Here are the details:

• Each of the tables must have the same number of Header rows. The data must start in the first non-header row.

The 25 in the formula must match the number of Data rows provided in the destination table . My example destination table has 26 rows. The first is a header row, containing no data. The rest (25 rows from 2 to 26) to receive data.

• The COLUMN function returns the number of the column containing the formula. The -1 value reduces the COLUMN value by 1 to provide a number that, multiplied by 25, will tell how many of the words in the list have been transferred into previous columns of this table

formula as entered in A2 of my example:

INDEX(Table 1::$A,ROW()+25×(COLUMN()−1))

Regards,

Barry