Array formula that alphabetizes a list

[Guest]

Is there an array formula I could use that would
alphebatize a list?

 

[Duke Carey]

Why wouldn't you simply sort it ascending?

If that has the potential to mess with formulas elsewhere in your workbook,
simply copy the list as values to a new sheet and sort it there.

 

[Biff]

Hi!

Using the menu commands to sort would surely be the best
way to go. But, if you really want a formula ....

This formula sorts ascending UNIQUE values be they text or
numbers or both. Posted by the late, great Frank Kabel.

Assume the list to sort is in the range A1:A21 with no
blank cells within the range.

Entered as an array with the key combo of CTRL,SHIFT,ENTER:

=INDEX($A$1:$A$21,MATCH(MIN(COUNTIF
($A$1:$A$21,"<"&$A$1:$A$21&"")+ COUNT($A$1:$A$21)*ISTEXT
($A$1:$A$21)+100000*ISBLANK($A$1:$A$21)),COUNTIF
($A$1:$A$21,"<"&$A$1:$A$21&"")+COUNT($A$1:$A$21)*ISTEXT
($A$1:$A$21)+100000*ISBLANK($A$1:$A$21),0))

Copy down as needed.

100000 is an arbitrary large number.

Isn't that a thing of beauty?

Biff