Forum Discussion
Custom Sort Alphabet Alphabetizing
jonahw218 My suggestion would be that you create a sorting key for every word, where each character is replaced by the "rank" it has in your customised alphabet. Obviously, this needs some extra columns as demonstrated in the attached workbook. I've used the new dynamic array (DA) functionality, TEXTJOIN and SORTBY in the example. Add a word to the text list or characters to your "alphabet" and the formulae ranges should automatically expand. See if it works for you. If your excel doesn't recognise DA or the TEXTJOIN and SORTBY functions, it can also be done without it, but it requires more copying and pasting.
=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)
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!