MacOS: How to sort Numbers in sequential order?

Question

Level 1

45 points

MacOS: How to sort Numbers in sequential order?

I am using Numbers to store trivia questions. Each question belongs to one of 6 categories.

Here is a partial extract of my spreadsheet:

I would like to sort the categories so everything is organized in a sequential order.(i.e. so you get an English question first, then a Math question, then a science question and so on).

E.g. I want it to look like this:

(Again this is just a partial extract).

How can I sort it like this? All the sorting tools just seem to put all the Catgeory 1s together, the catgeory 2s together and so on.

Thanks!

MacBook Pro 15″, macOS 12.5

Posted on Feb 24, 2023 5:00 AM

Reply

Answer 1

Top-ranking reply

SGIII

Level 8

36,104 points

Feb 24, 2023 6:55 AM in response to big_smile

Add an extra column to your questions table (that you can later hide if you want).

In that column use this formula:

In C2, filled down the column:

=COUNTIF(OFFSET(A$1,0,0,ROW(),1),A2)&"|"&A2

Then Sort Ascending on the new sort column.

Giving you something like this:

Bonus tip: If you want vary the combination of questions you can add another column with the formula =RAND() in each cell. Sort on that column. Then sort on the Sort column again.

SG

Reply

Answer 2

big_smile Author

Level 1

45 points

Feb 24, 2023 10:48 AM in response to SGIII

Thank you so much! It works very well, however, I do run into some problem areas later on in my spreadsheet

For example, where categories come clustered together. It starts off like this:

When sorted it ends up like this:

So in this example, the English come together instead of being spread out. Same with 2 x Science and 2 x Language.

(Sorry for not putting this in my original post. My spreadsheet is massive, and I hoped the extract I posted would be representative of the whole thing, but I guess I missed a few areas that would be problematic.

Reply

Answer 3

SGIII

Level 8

36,104 points

Feb 24, 2023 7:56 PM in response to big_smile

I believe the formula is calculating correctly if you maintain consistent category names in column A.

In your original post for the category names you had:

1 English

2 Math

3 Science

4 Languages

5 Geography

6 History

In your latest screenshot I see:

1 English

2 Math

2 Science

4 Languages

5 Geography

2 History

The duplication of the 2 confuses the formula (and me😀). If that is intentional could you explain your system?

Assuming it wasn't intentional, this is what I get after correcting the category names:

SG

Reply

Answer 4

big_smile Author

Level 1

45 points

Feb 25, 2023 4:39 AM in response to SGIII

Thank you so much! Whoops, somewhere along the lines in my monster-sized spreadsheet, the numbers for the categories got mixed up. I did a "find and replace" to correct them and the formula works perfectly now. I also did "find and replace" to remove trailing spaces, as that was messing things up too. (Normally I'd use "pop-up menu" in the cell options to avoid this, but this spreadsheet has been knited from different sources!)

Thanks again for all your help and also for your patience with my goofs!

Reply

Answer 5

SGIII

Level 8

36,104 points

Feb 25, 2023 5:14 AM in response to big_smile

Sounds familiar. In the real world working with a lot of data gets messy! No matter how I try to be careful and minimize errors, mistakes creep in and I end up spending time on data cleanup. Thanks for the green tick!

SG

Reply

MacOS: How to sort Numbers in sequential order?

Similar questions