Q: Can data be sorted by multiple columns in numbers version 3

if I were you then I would try asking here

https://discussions.apple.com/community/iwork/numbers

Hi kurrajong60,

Multiple column sort is not yet built into the interface of Numbers 3 but Apple has announced in this support document that it will be reintroduced in upcoming versoions.

Meanwhile, there is an ugly but easy workaround,

Say you have columns A,B, and C and you want to sort first by A then within A by C then within that by B.

Add a column D in which you put the formula =A&C&B.  Then sort by column D.

If one of your columns you need to sort has numbers let me know and I can give you a workaround on how to make those sort properly using this method.

SG

Hello

Given the primary sort key (column) is A, secondary sort key is B and tertiary sort key is C, you can sort the table firstly by C, then by B and finally by A, each in its own ascending or descending order. It will yield the same result as the sort by three keys (A, B, C) because proper spreadsheet programme is to perform stable sort (that is to preserve the original order of the elements which have the same key value).

Regards,

H

Here's a simple example. Say you want this sorted first by Region then by Name then by Amount.

So if you set up a sort index column with a simple formula and all you have to do is this (two clicks):

Giving you this:

Of course you could do it as I think Hiroto is suggesting and manually sort first by column B, then sort by C, then sort by A.

But that's a lot of trouble and it's easy to confuse the order so you end up having to start over again.

SG

Hi kurrajong60,

No problem. Post here if you end up having to sort columns with numbers of varying lengths that mess up the sort a little. There's an easy workaround for that if you run into that problem. 

And if we're lucky, Apple will restore multicolumn sort before too long.

SG

Note that there're serious limitations of sort by concatenated columns:

1) It won't let you sort by each key in different order. E.g., A in ascending, B in descending and C in ascending can not be achieved by concatenated key.

2) Concatenated key can give wrong sorting result depending upon the characteristics of data. E.g., in the first table in the screenshot below, sort by key = A&B or key = A&" "&B will yield wrong result while sort by key = A&CHAR(8232)&B yield correct result, where CHAR(8232) is U+2028 LINE SEPARATOR. The point is to use a field separator which a) is not present in data AND b) comes before any characters in data when sorted in ascending order. The second table demonstrates the failure case of key = A&CHAR(8232)&B, where requirement b) is not met because Numbers internally represent U+2028 as <sf:lnbr></sf:lnbr> and manually entered line break in cell as <sf:br></sf:br> and the latter comes before the former when sorted in ascending order.

Meanwhile the series of individual sort by each key in reverse significance order will always yield the correct result regardless of the characteristics of data set.

Regards,

H

Thanks, Hiroto, for the helpful explanations.

I like the tip on using a field separator not present in the data.

But the purpose of the simple workaround is just that: to serve as a simple, convenient workaround until Apple reintroduces multiple column sort.

The workaround is convenient to use in many (probably most) situations of everyday spreadsheet use. For columns with numbers, padding with leading "zeros" works in most situations and it's easy to do.

In situations where the workaround doesn't work, as in your examples, one can do the successive manual sorts by each column in reverse signficance order as you have described.

Let's hope Apple gets multi-column sort right when they reintroduce it.

Best,

SG