Numbers: custom sort order of a column

Question

Level 1

8 points

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

Answer 1

Best reply

Badunit

Level 7

26,536 points

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.

Reply

Answer 2

jayscott32 Author

Level 1

8 points

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?

Reply

Numbers: custom sort order of a column

Similar questions