Array formula that alphabetizes a list
Is there an array formula I could use that would
alphebatize a list? |
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? |
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? . |
All times are GMT +1. The time now is 01:43 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com