Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Adding continuous columns on a page in Numbers???

In Numbers I have several pages with 2 columns of alphabetical info. How can I cut the total pages in half by adding two new "continuous" columns on each page?

iMac 24″, macOS 12.0

Posted on Dec 13, 2021 2:35 PM

Reply
Question marked as Best reply

Posted on Dec 14, 2021 12:24 AM

"pages" are defined in Numbers only when you are printing or preparing to print a document.


In the 'editing' view columns on one Table can xtend well beyond the height of a 'page' or of a screen.


Your specification of "alphabetical info" implies that you expect to be able to sort that info alphabetically. Numbers uses a database protocol for sorting—each ROW contains a single record; each COLUMN contains data in one Field of the records.


Sorting a table rearranges the rows in an order based on the content of one of the columns (and can then do a sub sort based on the content of another column to rearrange the rows within the groups defined by identical content in the (sorted) cells of the first column.


To sort the data in two (or more) "continuous" columns into a single continuous order requires doing the sort when that data is contained in a single column table.


The sorted data can then be copied to one or more 'page sized' tables with the desired number of 'continuous' columns.


Here's a sample, using single word entries from a random text generator.


The initial text sample was converted to a one word to a line list using the Find and Replace process in Pages (which permits using a paragraph break character to be copied to the clipboard, then inserted into Find/Replace as the 'Replace with' value.


For the sample, the sorting page has 101 rows, including a Header row not included in the sort.

The Printing Table contains four columns of 26 rows, including a single header row, not included in the transfer.


The formula shown below the tables is entered in cell A2 of Table 2, and filled down and right to the last cell in column D. You'll need to adjust the '25' to match the number of data rows that will fit on a 'Page" of your printed document, and will have to adjust the "-1" on the second four column table to "+4-1", and increase that value by 4 for each one page column after that.


Sorting the single column table is done by selecting Column A, then clicking the small v that appears when you bring the pointer to the column reference tab, and choosing Sort Ascending.



Here are the results (top few rows only) after the single column table has been sorted:


For Printing, move the single column table far enough right that it will be on one or more pages to the right of the 'printing' table, and set the page count to go down the columns before moving to the second column, then print only the pages containing arts of the 'printing' table.


Regards,

Barry

7 replies
Question marked as Best reply

Dec 14, 2021 12:24 AM in response to colorogall

"pages" are defined in Numbers only when you are printing or preparing to print a document.


In the 'editing' view columns on one Table can xtend well beyond the height of a 'page' or of a screen.


Your specification of "alphabetical info" implies that you expect to be able to sort that info alphabetically. Numbers uses a database protocol for sorting—each ROW contains a single record; each COLUMN contains data in one Field of the records.


Sorting a table rearranges the rows in an order based on the content of one of the columns (and can then do a sub sort based on the content of another column to rearrange the rows within the groups defined by identical content in the (sorted) cells of the first column.


To sort the data in two (or more) "continuous" columns into a single continuous order requires doing the sort when that data is contained in a single column table.


The sorted data can then be copied to one or more 'page sized' tables with the desired number of 'continuous' columns.


Here's a sample, using single word entries from a random text generator.


The initial text sample was converted to a one word to a line list using the Find and Replace process in Pages (which permits using a paragraph break character to be copied to the clipboard, then inserted into Find/Replace as the 'Replace with' value.


For the sample, the sorting page has 101 rows, including a Header row not included in the sort.

The Printing Table contains four columns of 26 rows, including a single header row, not included in the transfer.


The formula shown below the tables is entered in cell A2 of Table 2, and filled down and right to the last cell in column D. You'll need to adjust the '25' to match the number of data rows that will fit on a 'Page" of your printed document, and will have to adjust the "-1" on the second four column table to "+4-1", and increase that value by 4 for each one page column after that.


Sorting the single column table is done by selecting Column A, then clicking the small v that appears when you bring the pointer to the column reference tab, and choosing Sort Ascending.



Here are the results (top few rows only) after the single column table has been sorted:


For Printing, move the single column table far enough right that it will be on one or more pages to the right of the 'printing' table, and set the page count to go down the columns before moving to the second column, then print only the pages containing arts of the 'printing' table.


Regards,

Barry

Dec 18, 2021 12:55 PM in response to colorogall

If your desire is to print a long two-column table as multiple columns on one page, you can create a table on another sheet that pulls in your data and "wraps" it into multiple columns. You print this new table instead. If it will still be more than one page, you can use multiple new tables, one per printed page. It is a bit of work.


The math can get pretty hairy if you want lots of automation or you can make it really simple. I'll present a relatively simple version here. I did it on one sheet to post it here. Table 2 would be on a different sheet.



Table 1 is the data. No formulas there.


Table 2 formulas & data:

Cell A2 has the row number of your first row of data. It happens to be row 2 in my example table so enter a 2 in this cell.

A3 =A2+1

D2 =A2+ROWS(A)−1

Copy/Paste that to G2

Copy/Paste the formula in A3 to the rest of the cells in columns A, D, and G


B2 =IFERROR(OFFSET(Table 1::$A$1,A2−1,0),"")

C2 =IFERROR(OFFSET(Table 1::$B$1,A2−1,0),"")

Copy both cells and paste to the rest of columns B:C and then to columns E:F and H:I


You can insert/delete rows and the formulas should adjust. If you add more columns you'll need to copy/paste formulas into them.


If you need additional tables for the rest of your data, copy/paste this table to create a duplicate table then change the number in cell A2 of that table to be the next row number.


Hide the columns that have the row numbers. Arrange each of these tables on separate sheets or on the same sheet so they print one per page.


If your data table has extra "blank" rows, the formulas above will transfer the blanks as zeros. If that is a problem, a slight modification to the formulas can take care of that:


B2 =IFERROR(OFFSET(Table 1::$A$1,A2−1,0),"")&""

C2 =IFERROR(OFFSET(Table 1::$B$1,A2−1,0),"")&""


This turns the results into "text". Text aligns to the left so you will need to right justify the "currency" columns.





Dec 14, 2021 8:42 AM in response to colorogall

Thank you for your response. I will play with that idea for future use. However, my problem is that I have TWO continuous columns that have been sorted by Column A. In other words, I have — Item Name in Column A, and Item Price in Column B. There are about four hundred of them and I would prefer not to have a lot of pages with just the two columns. So I am trying to find out if I can put about six or eight columns on a single page with both columns running continuously based on their sorting?


I appreciate your help.


ra


Dec 14, 2021 1:42 PM in response to colorogall

You can put as many pairs of columns as will fit on a 'page', but a sort on the contents of column A will sort all ROWS of the table to the order determined by the contents of column A.


If you make each pair of columns a separate table with two columns, sorting on column A of one table will sort ONLY that pair of columns.


Expanding my suggestion above, you could add a second column to the long table, then revise the formulas in the 'one page' table to transfer the data pairs to paired columns in the wider table.


All of your entries and sorting would be done on the long column.

The wide column would be placed on a separate Sheet, for ease in aligning it to the page definition in print preparation mode.


Regards,

Barry

Dec 17, 2021 1:41 PM in response to Barry

Thank you. I have used Mac since 1986, but just learning Numbers (always used Excel). I am a novice. I brought up the links you noted in your last response. The problem I have is I CANNOT create a new document that just has one column in it. How do create a document with a single column? I can delete all but Column B.

I also can't find how to create a Sheet 2 that your instructions say I need.

Thanks for your patience.


ra

Adding continuous columns on a page in Numbers???

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.