If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Array formula that alphabetizes a list
Is there an array formula I could use that would
alphebatize a list? |
#2
|
|||
|
|||
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. " wrote: Is there an array formula I could use that would alphebatize a list? |
#3
|
|||
|
|||
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)*I STEXT ($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 -----Original Message----- Is there an array formula I could use that would alphebatize a list? . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
array formula woes | Debra | General Discussion | 1 | August 11th, 2004 04:58 PM |
array formula to check if ranges are identical | Harlan Grove | Worksheet Functions | 2 | April 27th, 2004 05:58 AM |
Multiple List function | George | Worksheet Functions | 8 | February 15th, 2004 10:13 AM |
Funcs work differently as array formula? | Jonathan Rynd | Worksheet Functions | 3 | January 22nd, 2004 03:19 AM |