Convert 2 columns to 8 columns?

Question

Level 1

4 points

Convert 2 columns to 8 columns?

Hello,

Single sheet, multiple tables. One of the tables has 2 columns: checkbox next to a text column. Table is sorted by text column ascending, with blank rows at the bottom to allow for additional entries. It's too tall to fit without covering another table. Is there a way to convert the table to multiple, shorter columns while keeping them sorted in the original order?

I've tried simply entering different values for number of columns/rows, but Numbers won't accept a smaller number in the "rows" box than what's already there.

Duplicating rows and dragging the cells to new position, then deleting empty rows, results in sorting being lost after additional entries are made.

Thank you!

MacBook Pro 16″, macOS 10.15

Posted on Apr 26, 2022 04:46 AM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

29,910 points

Apr 26, 2022 07:10 PM in response to RSpeilman

You could have the long 2-column table on another sheet which is used for entering the text and checking the boxes and sorting. On your main sheet that you want to use for printing, you would have an 8-column table that uses complicated formulas to bring the data from the other table. You can't replicate checkboxes exactly but you can use text checkmarks instead. Here it is all on one sheet for simplicity of posting it here:

Formulas in Table 2:

A2 =IFERROR(INDEX(Table 1::$A,(COLUMN()−1)×0.5×(ROWS(A)−1)+ROW()),"")

B2 =IFERROR(IF(INDEX(Table 1::$B,(COLUMN()−2)×0.5×(ROWS(A)−1)+ROW()),"✓",""),"")

Copy/paste or drag/fill those formulas to the rest of column A and column B

Enter your desired header words into cells A1 and B1

Copy all of columns A and B at the same time

Paste to C1, E1, and G1 to complete the table

Do not have extra rows at the end of Table 1. Add rows only as needed.

You can add/delete rows to/from Table 2 and the data will continue to flow properly from column to column.

Reply

Answer 2

RSpeilman Author

Level 1

4 points

Apr 27, 2022 02:20 PM in response to Badunit

That worked perfectly. Thank you.

If I may, another question: as I mentioned above, I'm using this sheet to index themes, captions, image quality, etc. for a particular daily comic strip. The checklist in Table 2 will help me quickly note which themes appear in a specific image.

There's a separate table (Table 3) below the checkboxed table that lists the checked words that appear in Table 2.

Ultimately my goal is to make the image on the sheet findable by the theme words listed in Table 3. But the same words, obviously, appear in Table 2, along with all the other theme words.

Is there a way to exclude the entries in Table 2 from appearing in a file search?

(I'm trying to find a faster way to index by theme than adding tags to the images one by one in Preview.)

I know I can replace the B columns containing text with a png image of the text that allows the checkable columns A to remain visible and editable, but then I could no longer copy/paste from columns B and would have to type the entries just like I would if I were adding tags in Preview.

That's why I'd like to know if I can keep Table 2 as text while excluding its contents from a search.

Thank you

Reply

Answer 3

SGIII

Level 8

37,214 points

Apr 26, 2022 07:19 AM in response to RSpeilman

RSpeilman wrote:

Is there a way to convert the table to multiple, shorter columns while keeping them sorted in the original order?

With complicated formulas this is theoretically possible. But why would you want to do this? If the table is covering another table can't you just move the other table to a different position?

Note that you don't really need blank rows at the bottom to allow for additional entries. You can just click in the last row and hit <return>. A new row will be inserted automatically, including any formulas you have in the preceding rows.

SG

Reply

Answer 4

RSpeilman Author

Level 1

4 points

Apr 26, 2022 12:50 PM in response to SGIII

Thanks for your reply.

It's a design issue. Moving the tables around isn't an option because the sheet is being designed to ultimately be printed onto letter size paper. I need to fit all the tables into a visually pleasing layout. Maybe I should be using Pages?

I wanted the sheet to be easily updated as I found new information to enter (the cells I'm trying to order contain one-word summaries of the themes found in a daily cartoon strip, with a checkbox next to the word), but it sounds like I'll just have to alphabetize manually when revising. I'm an absolute beginner when it comes to spreadsheets, so complicated formulas are, at present, beyond me.

Reply

Answer 5

Barry

Level 9

60,011 points

Apr 29, 2022 12:57 AM in response to RSpeilman

" I'd like to know if I can keep Table 2 as text while excluding its contents from a search."

No. Searches using Find search the whole document.

You could, as you've noted, replace the tables you do not want to include in the search with images of those tables and their contents. Bit of a hassle, but possibly worth the annoyance.

If you will want to 'convert' those images back to tables, I'd suggest you start each search with making a duplicate of the document. That would allow you a slightly shorter task when it came to restoring the tables.

Regards,

Barry

Reply

Answer 6

RSpeilman Author

Level 1

4 points

Apr 29, 2022 02:00 AM in response to Barry

Thanks, Barry.

Your help was invaluable in my crash course in spreadsheets.

After much trial and error I've found that what I really wanted was not so much a spreadsheet but an interactive pdf with tables.

I've ended up migrating to Indesign (initially prompted by my discovery that you can add hyperlinks to images there), but your alphabetization formula still helps me fill the "Suggestions" table via copy/paste from Numbers.

In Indesign I've found that I can still copy from that table to enter the checked items into my "Searchable Selections" table, and then also into an easily-accessible metadata keywords field. I suspect that if I restrict my Finder search to keywords, I'll get the results that I wanted. Haven't tried it yet but I think it should work.

Reply

Convert 2 columns to 8 columns?

Similar questions