You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

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

Numbers: custom sort order of a column

Caveat: I already know how to sort the table using rules for the columns, alpha-numeric ascending or descending. I already know how to drag rows into a custom order. What I need is to be able to custom sort a single column. For example, if the column has cells with data the following entries (red, yellow, green, blue) and I choose sort ascending, the column will be sorted to blue, green, red, yellow. But what if I want the entries to be sorted in a non-alphabetic order, say green, blue, yellow, red? I know I can number the entries with an index (1 green, 2 blue, 3 yellow, 4 red), but is there a way to custom sort without that?

MacBook Pro 15″, macOS 11.1

Posted on Feb 26, 2021 10:02 AM

Reply
Question marked as Top-ranking reply

Posted on Feb 26, 2021 10:44 AM

No, there is not. What you could do is create a lookup table so you don't have to type the indexes into each row.



Formula in Table 1::B2 =XLOOKUP(A2,Sort Order::A,Sort Order::B,"")

Fill down to complete the column


Cell B2 in the Sort Order table has a space in it. This eliminates a bunch of zero results in Table 1. You could eliminate that row and use the formula =IF(A2<>"",XLOOKUP(A2,Sort Order::A,Sort Order::B,""),"") in place of the formula given above.


When sorting by the index in descending order, if you have blank rows like in my example, you will want to select only the filled in rows, not the entire column. If you sort the entire column, all the blank rows move to the top and the filled in rows move to the bottom.

2 replies
Question marked as Top-ranking reply

Feb 26, 2021 10:44 AM in response to jayscott32

No, there is not. What you could do is create a lookup table so you don't have to type the indexes into each row.



Formula in Table 1::B2 =XLOOKUP(A2,Sort Order::A,Sort Order::B,"")

Fill down to complete the column


Cell B2 in the Sort Order table has a space in it. This eliminates a bunch of zero results in Table 1. You could eliminate that row and use the formula =IF(A2<>"",XLOOKUP(A2,Sort Order::A,Sort Order::B,""),"") in place of the formula given above.


When sorting by the index in descending order, if you have blank rows like in my example, you will want to select only the filled in rows, not the entire column. If you sort the entire column, all the blank rows move to the top and the filled in rows move to the bottom.

Feb 26, 2021 11:12 AM in response to jayscott32

In an trial I used popup text strings in one column(daily, eod, weekly, etc.) which I wanted to custom sort non-alphabetically. Rather than stick an index at the front of each text string (1.daily, 2.weekly, 3. ...), I added a second (hidden) column with ridiculously long nested text substitutions: SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Frequency harvest and seed kefir,"daily",".1"), "eod", ".2"),"2x / Wk",".3"),"weekly", ".4"),"eow", ".5"),"monthly",".6"),"eom",".7"),"seasonally",".8"),"2x / Yr", ".9"),"yearly","1.0")

I can now sort the table by this second column to get the custom sorting based on column one, but this seems over-involved and very hacky for something which should have a more elegant / simple solution I may have overlooked. Anyone?

Numbers: custom sort order of a column

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