Sort by Formula (without VBA)

Question

I am attempting to sort a short column of values (descending) by using a formula in a different column. So if A1 through A10 contain numbers, in B1 I enter:

=LARGE($A$1:$A$10,ROWS($1:1))

and copy down:

As you see, this is the formula equivalent to copying column A to column B and then sorting B using Sort from the Ribbon.

If the values in column A are Text, I can do the same thing by using:

=LOOKUP(1,0/FREQUENCY(ROWS($1:1),COUNTIF($A$1:$A$10,">="&$A$1:$A$10)),$A$1:$A$10)

and copying down:

My problem is devising a formula that will handle both text and numbers. Here is an example of data in A and expected results in B:

10      zeta
alpha   gamma
zeta    beta
alpha   alpha
2       alpha
beta    10
1       4
gamma   4
4       2
4       1

This is easy with VBA, but I need a solution with worksheet formulas only.

Answer 1

This requires that the results start in at least the second row, only because I need a row above for the COUNT() function.

Also it is an array formula:

=IF(LOOKUP(1,0/FREQUENCY(ROWS($1:1),COUNTIF($A$1:$A$10,">="&IF(NOT(ISNUMBER($A$1:$A$10)),$A$1:$A$10))),$A$1:$A$10)<>0,LOOKUP(1,0/FREQUENCY(ROWS($1:1),COUNTIF($A$1:$A$10,">="&IF(NOT(ISNUMBER($A$1:$A$10)),$A$1:$A$10))),$A$1:$A$10),LARGE(IF(ISNUMBER($A$1:$A$10),$A$1:$A$10),COUNT($C$1:$C1)+1))

Being an Array use Ctrl-Shift-Enter when exiting edit mode. If done correctly then excel will put {} around the formula

How it works:

We took the formula to sort text and made it only find the non numeric data LOOKUP(1,0/FREQUENCY(ROWS($1:1),COUNTIF($A$1:$A$10,">="&IF(NOT(ISNUMBER($A$1:$A$10)),$A$1:$A$10))),$A$1:$A$10) That is done in the COUNTIF with the IF() statment.

This will return 0 after sorting all the non numeric number, so when it equals 0 we use the LARGE() Function only on the number data. LARGE(IF(ISNUMBER($A$1:$A$10),$A$1:$A$10),COUNT($C$1:$C1)+1).

Since COUNT() will only count number we use that as our k in the LARGE(). We count all the numbers above the current cell and add 1 to get the proper k.

Answer 2

Can you add an aid column, which modify the number values of column A?

If you use the formula

=TEXT(A1,"'0")

then your slightly modified second formula will work

=LOOKUP(1,0/FREQUENCY(ROWS($1:4),COUNTIF($B$1:$B$10,">="&$B$1:$B$10)),$A$1:$A$10)