SORT function in Numbers app

Spreadsheets are much better at sorting lists arranged in columns than in rows.

If you put your list in a column like this:

Then all you have to do is this (no formulas):

SG

You can get a similar result like this:

sorting smallest to largest use:

=SMALL(A2:D2,1)&SMALL(A2:D2,2)&SMALL(A2:D2,3)&SMALL(A2:D2,4)

sorting largest to smallest:

=LARGE(A2:D2,1)&LARGE(A2:D2,2)&LARGE(A2:D2,3)&LARGE(A2:D2,4)

Just use LARGE or SMALL to sort the values

Then combine as one string / number

Then hide what you don't need / want to see.

Ralf

I believe with additional step you should be able to sort anything but, for some reason that eludes me at this late hour, it is not working. I therefore have a question for the forum based on this experiment. Either the method does not really work or I messed up the formula(s) or there is bug in the COUNTIF function that makes this method fail with mixed data types (numbers and text, even if the numbers are formatted as text). Or maybe I am misusing "<=".  I hope someone here can figure out what is going wrong in row 7. 

A:D are the original arrangement. I:L are sorted ascending. All looks good until row 7

Formulas:

E2 =COUNTIF($A2:$D2,"<="&A2)

Fill right to H2

I2 =INDEX($A2:$D2,MATCH(SMALL($E2:$H2,I$1),$E2:$H2))

Fill right to L2

Fill down with those formulas

It is messing up in row 7 due to incorrect results in E7:H7. If I manually test each "<=" I get a different result than what COUNTIF is getting. Here is what I get for A7:

I get the answer is 2 but COUNTIF is giving the answer as 1. If I format A:D as text it does not change the answers (but it does get rid of the blue triangles).

I exported to Excel and it gives the same results for the COUNTIFS. In addition, the INDEX(MATCH)) formula in Excel fails in numerous cells for no reason I can explain. For example, in cell I2 the MATCH part cannot find a match unless I include the parameter for "exact match" or change it to XMATCH. Really strange.

I am baffled at the moment.

be right back