How to sort using formula?

  • Thread starter Thread starter Arie Sukendro
  • Start date Start date
A

Arie Sukendro

Does anybody know a trick to sort data using formula?

Example:
User input: 2,5,1,3,4
Formula result (referring to user input cells): 1,2,3,4,5

Thanks
Arie
 
Arie,

I'm not sure about what you want but I'll give it a try.
Suppose your user entry is in column A1:A5

Select B1:B5 ( B1 will be the highlighted "active" cell) and type
=Small(A1:A5,{1,2,3,4,5}).
Confirm the formula not with <ENTER> but with <CNTRL>+<SHIFT>+<ENTER>.
Tis is called array input of the formula.

In cell B1:B5 the figures you gave are now ordered (ascending).

I hope this is what you wanted.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
That will result in a #NUM! error in row 2.

OTOH,

=LARGE(A$1:A$5,ROW())

in row 1,copied down to row 5,will sort the values, but in descending
order, i.e, in the opposite direction from the OP's request.

=SMALL(A$1:A$5,ROW())

will sort the list ascending.
 
Auk Ales,
your formula works perfectly.
Thank you for your help and also thanks to all other participants.

Regards,
Arie
 
YW Arie Sukendro and thank for the feedback.
It's always nice to hear that things are solved.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Back
Top