LinkBack URL
About LinkBacks
Looking for formula (Array and Non-Array Formula welcome) to automatically sort data in alphabetical order. See sample file. Thanks.
Looking for formula (Array and Non-Array Formula welcome) to automatically sort data in alphabetical order. See sample file. Thanks.
One way:
=LOOKUP(1,0/FREQUENCY(ROWS(B$1:B1),COUNTIF($A$2:$A$5,"<="&$A$2:$A$5)),$A$2:$A$5)&""
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Easiest way, add Sort index using following formula.
=COUNTIF($A$2:$A$5,"<="&A2)
Then use Index,Match. To return based on sort index.
If using Office 365, use SORT() function.
Alternately...
Edit: Use Glenn's formula. Much more efficient. Mine was only looking at left most char.
Copy down.
Last edited by CK76; 10-26-2020 at 11:44 AM.
"Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something."
― Robert A. Heinlein
Glenn Kennedy: when I changed the rows fromto
Please Login or Register to view this content.or when I changed the rows to cover 21 rows, it is not displaying the correct answer. See attached file.Please Login or Register to view this content.
Please Login or Register to view this content.
It doesn't like blanks... The best way to overcome this is to use a Named Range to auto-adjust the ranges. In your rela sheet, will column A be data, or the product of a formula? Will it be text or a number?
Please try at B2
=LOOKUP(1,0/FREQUENCY(COUNTBLANK($A$2:$A$21)+ROWS(B$2:B2),1/(1/COUNTIFS($A$2:$A$21,"<="&$A$2:$A$21))),$A$2:$A$21)
CK76:
Easiest way, add Sort index using following formula. - I have tried this helper column approach. However, it is not displaying the desired results the way I would like it displayed. Sorted values are pushed to the bottom of the list. See attached file to see issue with approach.
=COUNTIF($A$2:$A$5,"<="&A2)
You need to exclude blanks.
Ex:
=IF(A2="","",COUNTIFS($A$2:$A$21,"<="&A2))
Then in C2:
=IFERROR(INDEX($A$2:$A$21,MATCH(SMALL($B$2:$B$21,ROWS($A$1:A1)),$B$2:$B$21,0)),"")
See attached.
CK76: works like a charm. Thanks a lot.
Bo_Ry: excellent solution. Thanks a lot.
Glenn Kennedy: Thanks a lot for your proposed solution. I did not really clear things up for you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks