Sort text list alphabetically using a formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

paddyyates - 19 Dec 2005 15:12 GMT

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.

Signature

paddyyates

Reply to this Message

Domenic - 19 Dec 2005 16:52 GMT

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,MATCH(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.20am2y_1135005304.861@excelforum-nospam.com>,
paddyyates <paddyyates.20am2y_1135005304.861@excelforum-nospam.com>
wrote:

> Does anyone know of a formula you can use to sort a list of text
> alphabetically?
[quoted text clipped - 3 lines]
>
> Seems like a straightforward enough idea but I can't find anything.

Reply to this Message

paddyyates - 19 Dec 2005 17:11 GMT

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

Signature

paddyyates

Reply to this Message

Hash@example.org - 21 Dec 2005 03:12 GMT

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.20am2y_1135005304.861@excelforum-nospam.com>,
paddyyates <paddyyates.20am2y_1135005304.861@excelforum-nospam.com>
wrote:

> Does anyone know of a formula you can use to sort a list of text
> alphabetically?
[quoted text clipped - 3 lines]
>
> Seems like a straightforward enough idea but I can't find anything.

Reply to this Message