Forum Discussion

jonahw218's avatar
jonahw218
Copper Contributor
Feb 29, 2020

Custom Sort Alphabet Alphabetizing

So I want to alphabetize a list with a custom list of symbols. I've tried the custom list function but this only sorts cells if the exact word/symbols are in that custom list.   For example, if I h...
excel
jonahw218's avatar
jonahw218
Copper Contributor
Feb 29, 2020
Could you explain what each of these functions are doing individually and as a whole in these equations and what the differences between them are if you're up to it 🙂 Thanks!

=IFERROR(MATCH(MID(Table1[Text],COLUMN()-COLUMN($C$1),1),Table2[My Alphabet],0),0)
&
=IFERROR(MATCH(MID($A2,COLUMN()-COLUMN($C$1),1),Table2[My Alphabet],0),0)
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Feb 29, 2020

jonahw218 The first formula uses the newest dynamic array functionality in Excel. The second should work in all versions. What it does is look at your text, and assign a "key character", based on your alphabet, to each of the characters in your text. Not easy to explain, though, in words.. In my original example a P would translate to 3 and an E to 2 etc. So "PE" becomes "32". "PS" would become "31" and thus come before PE when you sort it "your way".

 

In my latest file I improve the fomulae, inspired by Ghazanfar Abidi. They, basically, achieve the same. You can expand the "My Alphabet to how many characters you want, as long as you make sure that the "Key" is in the correct order, so that Excel will sort everything the correct way. 

 

If this doesn't answer your question, I can give it another try. Let me know!

 

Resources