Sort text list alphabetically using a formula
 

Does anyone know of a formula you can use to sort a list of text
alphabetically?

I know the data/sort option but I want something that can pick up new
entries in the original data list without the use having to intervene.

Seems like a straightforward enough idea but I can't find anything.


--
paddyyates
------------------------------------------------------------------------
paddyyates's Profile: http://www.excelforum.com/member.php...o&userid=29744
View this thread: http://www.excelforum.com/showthread...hreadid=494589

Sort text list alphabetically using a formula
 

First, set up a dynamic range for your list of text and give it a name,
such as MyRange. Then enter the following formula in a cell, let's say
B1, and copy down:

=IF(ROWS($B$1:B1)<=COUNTA(MyRange),INDEX(MyRange,M ATCH(SMALL(COUNTIF(MyRa
nge,"<"&MyRange),ROWS($B$1:B1)),COUNTIF(MyRange,"< "&MyRange),0)),"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Post back if you
need help creating a dynamic range.

Hope this helps!

In article ,
paddyyates
wrote:

Does anyone know of a formula you can use to sort a list of text
alphabetically?

I know the data/sort option but I want something that can pick up new
entries in the original data list without the use having to intervene.

Seems like a straightforward enough idea but I can't find anything.

Sort text list alphabetically using a formula
 

That works, thanks. It seems the answer wasn't that straightforward!


--
paddyyates
------------------------------------------------------------------------
paddyyates's Profile: http://www.excelforum.com/member.php...o&userid=29744
View this thread: http://www.excelforum.com/showthread...hreadid=494589

Sort text list alphabetically using a formula
 

A somewhat simpler method, requiring helper columns. Assume your text
in A:A.

In B1: = IF(LEN(A1)0,COUNTIF(A:A,"<"&A1),"")
gives a lexographical sorting number to each entry

In C1: = IF(LEN(A1),SMALL(B:B,ROW()),"")
Sorts the values.

In D1: = IF(LEN(A1)0,INDEX(A:A,MATCH(C1,B:B,0),1),"")

Fill down past your data. Can continue in this way to remove dupes.

....best, Hash

In article ,
paddyyates
wrote:

Does anyone know of a formula you can use to sort a list of text
alphabetically?

I know the data/sort option but I want something that can pick up new
entries in the original data list without the use having to intervene.

Seems like a straightforward enough idea but I can't find anything.