Sorting a column by using formula

  • Thread starter Thread starter Praise
  • Start date Start date
Praise,

Usually a sort is done by use of the sort tool that is found on th
data menu.

It is possible to use formulas to automatically lsit a range in a sor
order, but it is difficult to achieve this for large lists, or wher
there a duplicates in the list.

Please send an example of your problem to help in determining th
solution
 
Hi
you can create a sorted extract in another column.
Some note upfront:
- very slow array formulas
- work only for small ranges (otherwise to slow)
- very LONG) formulas

But saying that now some formulas (copying excel's sort behaviour)
Assumptions:
- Data is in A3:A20 (adapt this to your needs)
- BigNumber is a defined name refering to the value 70000

Enter the following array formula (entered with CTRL+SHIFT+ENTER) in
your first row for the sorting extract):
= INDEX(IF(ISBLANK($A$3:$A$20),"",$A$3:$A$20),MATCH(SMALL
(COUNTIF($A$3:$A$20,"<"&$A$3:$A$20)+0*BigNumber*ISNUMBER($A$3:$A$20)+1*
BigNumber*ISTEXT($A$3:$A$20)+2*BigNumber*ISLOGICAL($A$3:$A$20)+3*BigNum
ber*ISERROR($A$3:$A$20)+4*BigNumber*ISBLANK($A$3:$A$20),ROW(1:1)),COUNT
IF($A$3:$A$20,"<"&$A$3:$A$20)+0*BigNumber*ISNUMBER($A$3:$A$20)+1*BigNum
ber*ISTEXT($A$3:$A$20)+2*BigNumber*ISLOGICAL($A$3:$A$20)+3*BigNumber*IS
ERROR($A$3:$A$20)+4*BigNumber*ISBLANK($A$3:$A$20),0))



Copy this formula down for all required rows (this is one single
formula)
 
Back
Top