Two stage sorting function which eliminates gaps and sorts duplicate ranks?

Hi folks. Here is my scenario I'm trying to sort with using only formulas (older external program will not read .xlsm or .xlsx). I'll spare you the details of my own hackneyed attempt but let's just say I felt I was getting close with INDEX, MATCH, FIND and VLOOKUP but hit a wall.

Columns A and B: categories in order I need the data sorted by (non-alphabetic):
DO = 001
RAY = 002
MI = 003
FA = 004
SO = 005
LA = 006
TI = 007

Column C: Non-sorted data
RAY01
RAY05
RAY03
DO01
FA02
TI05

Column D: a helper column of some kind I imagine, for example the first three digits are by finding the text in Column A and inserting corresponding number in column B, after decimal is the "rank" within each category:
002.1
002.3
002.2
001.1
004.1
007.1

Column E, output data sorted according to Column D
DO01
RAY01
RAY03
RAY05
FA02
TI05

Maybe I need another helper column? Maybe I'm approaching it totally wrong? The variables being different lengths, skips in the ranking, duplicate rankings, my brute force methods are not cutting it. Any insight would be great!

Thanks in advance.
W. Johnson

Thanks for the reply. Maybe I made the example seem too simple. A real world project may have 50+ variables (DO, RAY, MI) to sort and add to that the list may change, lengthen, shorten per project. My aim was to have the list visible and customizable by the end user. Actually, the variable list in Column A is the only input the user would need to control. The raw data is imported by a FileNameList and the output sorted column would be copied and pasted into another worksheet by the user. My goal was to have the rest automated.

Thought I would attach this progress file in case anyone wanted to see the direction I was trying to go. Thanks!

Try these ARRAY formulas
IN K2, then drag down

Please Login or Register  to view this content.

IN I2 , then drag across for I & J columns

Please Login or Register  to view this content.

ARRAY formula is used

To enter ARRAY formula
Paste the formula
Press F2
Press Ctrl+Shift+Enter keys together.
formula will be covered with{} brackets.

Thanks. This didn't quite get me where I needed to go but I found another route (used defined names to avoid monster formulas), creating a list with the dual ranking coded into the number 2.001, 2.002,etc. sorting and indexing. If you're curious to know specifics, reply back to this message and I'll send you an example.

Thanks again for your help!

See attached. Thanks everyone!