Two Stage Sorting by Formula Only

[wdjohnson]

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)
This list will be the only one customizable by the end user, typically 50+ entries per project and list would change/lengthen/shorten frequently from project to project:

DO = 001
RAY = 002
MI = 003
FA = 004
SO = 005
LA = 006
TI = 007

Column C: Non-sorted data imported from FileNameList
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

Hopefully this makes sense. 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

 

[mikerickson]

Does the imported data in column C always have two digit numbers at the end? Or might there be a RAY123

 

[wdjohnson]

Thanks for the reply. Yes there may be one two or three digits depending on the imported data. I have a test file with a few entries and the direction I was trying to go but I can't figure out how to upload it haha.

 

[mikerickson]

When sorting, which comes first, RAY01 or RAY001?
Is RAY010 the same or different than RAY01

 

[wdjohnson]

The imported data could come in as so

XY12345_RAY99.pdf
XY12345_RAY100.pdf

And I'm using a MID function to chop off the prefix XY12345_ and suffix .pdf

The creater of the file should have named 99 as 099 but they didn't. Hopefully the function can bypass that anyway (or if not, throw a warning to go rename the file numbers)?

 

[mikerickson]

Here is a solution that uses lots of helper columns

Unknown
	A	B	C	D	E	F	G	H	I	J	K
1	DO	001	RAY01	RAY	002	001	2.001	1.001	DO	DO.001	DO001
2	RAY	002	RAY05	RAY	002	005	2.005	2.001	RAY	RAY.001	RAY001
3	MI	003	RAY03	RAY	002	003	2.003	2.003	RAY	RAY.003	RAY003
4	FA	004	DO01	DO	001	001	1.001	2.005	RAY	RAY.005	RAY005
5	SO	005	FA02	FA	004	002	4.002	2.999	RAY	RAY.999	RAY999
6	LA	006	TI05	TI	007	005	7.005	3.099	MI	MI.099	MI099
7	TI	007	RAY999	RAY	002	999	2.999	4.002	FA	FA.002	FA002
8			MI99	MI	003	099	3.099	7.005	TI	TI.005	TI005
9
Sheet1

Cell Formulas
Range	Formula
F1	=RIGHT("000"&SUBSTITUTE($C1,D1,""),3)
G1	=0+(E1&"."&F1)
H1	=SMALL(G:G, ROWS($1:1))
I1	=INDEX($A:$A,INT(H1),1)
J1	=I1&TEXT(MOD(H1,1),".000")
K1	=SUBSTITUTE(J1,".","")
D1	{=LEFT(Sheet1!$C1, MIN((FIND({1,2,3,4,5,6,7,8,9,0}, Sheet1!$C1&"12345567890")))-1)}
E1	{=VLOOKUP(D1,$A$1:$B$7,2,FALSE)}
Press CTRL+SHIFT+ENTER to enter array formulas.

Columns A and B are the letter to number chart
Column C is the imported data
Column D is the text part of the data
Column E converts that text to its matching number
Column F is the number part of column C
Column G combines those into a single number
Column H sorts column G
Column I is the text portion per column H
Column J combines column I with the decimal part of column H
And (finally) column K removes the decimal point.

That's a lot of columns. They can be combined. To avoid monster formulas, one can define two Names
Select a cell in row 1 and define the two names. (Note the absolute column/relative row referencing)
Name: LetterPart RefersTo: =LEFT(Sheet1!$C1,MIN((FIND({1,2,3,4,5,6,7,8,9,0}, Sheet1!$C1&"12345567890")))-1)
Name: NumberPart RefersTo: =RIGHT("000"&SUBSTITUTE(Sheet1!$C1,LetterPart,""),3)

Unknown
	A	B	C	D	E	F
1	DO	001	RAY01	2.001	1.001	DO001
2	RAY	002	RAY05	2.005	2.001	RAY001
3	MI	003	RAY03	2.003	2.003	RAY003
4	FA	004	DO01	1.001	2.005	RAY005
5	SO	005	FA02	4.002	2.999	RAY999
6	LA	006	TI05	7.005	3.099	MI099
7	TI	007	RAY999	2.999	4.002	FA002
8			MI99	3.099	7.005	TI005
Sheet1

Cell Formulas
Range	Formula
D1	=0+(VLOOKUP(LetterPart,$A$1:$B$7,2,FALSE)&"."&NumberPart)
E1	=SMALL(D:D,ROWS($1:1))
F1	=SUBSTITUTE(SUBSTITUTE(E1,INT(E1),INDEX($A$1:$B$7,INT(E1),1),1),".","")

Named Ranges
Name	Refers To	Cells
LetterPart	=LEFT(Sheet1!$C1,MIN((FIND({1,2,3,4,5,6,7,8,9,0}, Sheet1!$C1&"12345567890")))-1)
NumberPart	=RIGHT("000"&SUBSTITUTE(Sheet1!$C1,LetterPart,""),3)

Column D is the data converted to numbers
Column E is that sorted
Column F returns those number to text+number format.

 

[wdjohnson]

Thanks so much. Good call on the defined names up to Column D. From there I did a simple 1 thru X RANK in Column E. Lastly an INDEX,MATCH,ROWS to sort

=IFERROR(INDEX($C$1:$C$1000,MATCH(ROWS($E$1:E1),$E$1:$E$1000,0)),"")

Thanks for helping out a pure stranger!