Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Invisable text in formula bar | Excel Discussion (Misc queries) | |||
need help righting script to sort rows alphabetically then seperate them to line up | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula Integrity Not Preserved During Sort in Excel 2000 | Excel Discussion (Misc queries) | |||
Counting Repeated text or duplicates in a list | Excel Discussion (Misc queries) |