How to sort using formula?

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
 
A

A.W.J. Ales

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 *
 
J

JE McGimpsey

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.
 
A

Arie Sukendro

Auk Ales,
your formula works perfectly.
Thank you for your help and also thanks to all other participants.

Regards,
Arie
 
A

A.W.J. Ales

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 *
 
Top