Rank lowest to highest with similar values

[Stuepef]

I have values in column A that vary in length and characters. I want to rank those values from lowest to highest, but i am running into duplicates even though the values are different. I have different formulas in columns C & D to dissect the information in different ways, but its not working how I want it to. I am looking for assistance to differentiate these values to accurately rank them from lowest to highest.

Workbook:

[TABLE="width: 668"]
<tbody>[TR]
[TD][/TD]
[TD]Formula 1[/TD]
[TD]Formula 2[/TD]
[TD]Rank on formula 1[/TD]
[TD]Rank on formula 2[/TD]
[/TR]
[TR]
[TD]Pontiac $500-$20[/TD]
[TD]500[/TD]
[TD]500-20[/TD]
[TD]1[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Feet $500-$40[/TD]
[TD]500[/TD]
[TD]500-40[/TD]
[TD]1[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]USA Feet $2,000-100% Gt Plus[/TD]
[TD]2000[/TD]
[TD]2000-100[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]USA Feet $2,000-100%[/TD]
[TD]2000[/TD]
[TD]2000-100[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Orange $3,000-70% Treat ACE Free[/TD]
[TD]3000[/TD]
[TD]3000-70[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]USA Emboss. Orange $3,000-80%[/TD]
[TD]3000[/TD]
[TD]3000-80[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]USA Emboss. Orange $4,000-100% Gt Plus[/TD]
[TD]4000[/TD]
[TD]4000-100[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]USA Emboss. Orange $4,000-100%[/TD]
[TD]4000[/TD]
[TD]4000-100[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

Formula 1:

=IFERROR(INT(LEFT(REPLACE(SUBSTITUTE(A2,"-"," "),1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&1/17))-1,""),5)),INT(LEFT(REPLACE(SUBSTITUTE(A2,"-"," "),1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&1/17))-1,""),4)))

Formula 2:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),FIND(" ",A2&" ",MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")))-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))),"$",""),"%",""),",","")

Rank formula for column C (this one is the closest in accuracy, except when the value is 3 digits or the exact same):

=IF(C3="","",SUMPRODUCT(($C$2:$C$9< C3)/COUNTIF($C$2:$C$9,$C$2:$C$9))+1)

Desired Result:

[TABLE="width: 326"]
<tbody>[TR]
[TD][/TD]
[TD]Rank[/TD]
[/TR]
[TR]
[TD]Pontiac $500-$20[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Feet $500-$40[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]USA Feet $2,000-100%[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]USA Feet $2,000-100% Gt Plus[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Orange $3,000-70% Treat ACE Free[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]USA Emboss. Orange $3,000-80%[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]USA Emboss. Orange $4,000-100%[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]USA Emboss. Orange $4,000-100% Gt Plus[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]

Thank you!

 

[DRSteele]

This task requires a different approach. What you're really after is a ranking with cascading conditions.

For example, sports leagues (and sales managers, among many others) use cascading rules to break ties. In a sports example, you could use the following rules: First by POINTS. Then by GAMES REMAINING. Then by WINS. Then by GOALS FOR. Then by GOAL DIFFERENTIAL. When trying to do this any other way, the problems mount with each successive rule. So I invented a method that works well enough. I create a new stat for each member (I call it GRADE) which I then use to rank the members of the group. GRADE is calculated by adding up all the various tiebreaker stats: but not just a straight summation (which wouldn’t work, obviously), rather a summation of each stat in declining order of magnitude. The magnitudes are computed so as to ensure that each one cannot get so large or small that it ‘ticks over’ the prior stat.

For your data, the first rule is by Criteria1, so I used your Formula1 to extract 4000 for USA Emboss. The second is Criteria2, so I used your Formula2 to extract the 100. The final is Criteria3, so I used a keyword (i.e., "plus") to report a 1 or 1 0 for whether or not it's a GT Plus. The result is 4000 + 100/(10^3) + 1/(10^(3+1)) for a Grade of 4000.1001. The other Grades are arrived at similarly and then they are ranked in ascending order.

Book1
	A	B	C	D	E	F
1		orders of magnitude:	3	1
2		keyword:		plus
3
4	Product	Criteria1	Criteria2	Criteria3	Grade	Rank (ascending)
5	Pontiac $500-$20	500	20	0	500.0200	1
6	Feet $500-$40	500	40	0	500.0400	2
7	USA Feet $2,000-100% Gt Plus	2000	100	1	2000.1001	4
8	USA Feet $2,000-100%	2000	100	0	2000.1000	3
9	Orange $3,000-70% Treat ACE Free	3000	70	0	3000.0700	5
10	USA Emboss. Orange $3,000-80%	3000	80	0	3000.0800	6
11	USA Emboss. Orange $4,000-100% Gt Plus	4000	100	1	4000.1001	8
12	USA Emboss. Orange $4,000-100%	4000	100	0	4000.1000	7
Sheet54

Cell Formulas
Range	Formula
D5	=IF(ISNUMBER(SEARCH($D$2,A5)),1,0)
D1	{=MAX(LEN(D5:D12))}
E5	=B5+C5/(10^$C$1)+D5/(10^($C$1+$D$1))
F5	=RANK.EQ(E5,$E$5:$E$12,1)
C1	{=MAX(LEN(C5:C12))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Stuepef]

@DRSteele - thank you for the response. What happens if there are duplicates in the same list? For example, what if there are 2 or 3 Pontiac $500-$20? Would your logic still apply?

[TABLE="width: 437"]
<tbody>[TR]
[TD]Employee[/TD]
[TD]Products[/TD]
[TD]Rank[/TD]
[/TR]
[TR]
[TD]Employee A[/TD]
[TD]Feet $500-$40[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Employee B[/TD]
[TD]Feet $500-$40[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Employee C[/TD]
[TD]Pontiac $500-$20[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Employee D[/TD]
[TD]USA Feet $2,000-100%[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Employee E[/TD]
[TD]USA Feet $2,000-100%[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Employee F[/TD]
[TD]Pontiac $500-$20[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Employee G[/TD]
[TD]Pontiac $500-$20[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[DRSteele]

Well yes: the function RANK.EQ will rank them as identical because they are identical. If they are identical and you have no more rules to discriminate amongst them, then they will get the same Grade and same Rank.

 

[Stuepef]

DRSteele - I have other values that would need the same logic, but I am assuming I would just have to change the keywords. Here are the other values:

[TABLE="width: 248"]
<tbody>[TR]
[TD]LazyMaster Blue $300[/TD]
[/TR]
[TR]
[TD]LazyMaster Blue $500[/TD]
[/TR]
[TR]
[TD]LazyMaster Blue $750[/TD]
[/TR]
[TR]
[TD]LazyMaster Blue $1,000[/TD]
[/TR]
[TR]
[TD]LazyMaster Phone $2,300[/TD]
[/TR]
[TR]
[TD]LazyMaster Phone $2,700[/TD]
[/TR]
[TR]
[TD]LazyMaster USA Blue $1,500[/TD]
[/TR]
[TR]
[TD]LazyMaster USA Blue $2,200[/TD]
[/TR]
[TR]
[TD]LazyMaster USA Emboss. Yellow $2,700[/TD]
[/TR]
[TR]
[TD]LazyMaster USA Emboss. Yellow $3,600[/TD]
[/TR]
[TR]
[TD]LazyMaster USA Yellow $3,675[/TD]
[/TR]
[TR]
[TD]LazyMaster USA Emboss. Yellow $4,500[/TD]
[/TR]
[TR]
[TD]LazyMaster USA Emboss. Orange $5,200[/TD]
[/TR]
[TR]
[TD]LazyMaster USA Emboss. Orange $6,650[/TD]
[/TR]
[TR]
[TD]CoolValue Blue $1,000[/TD]
[/TR]
[TR]
[TD]CoolValue Yellow $2,300[/TD]
[/TR]
[TR]
[TD]CoolValue USA Yellow $3,600[/TD]
[/TR]
[TR]
[TD]CoolValue USA Orange $5,200[/TD]
[/TR]
[TR]
[TD]CoolValue USA Orange $6,650[/TD]
[/TR]
[TR]
[TD]Made Value Blue $1,000[/TD]
[/TR]
[TR]
[TD]Made Value Yellow $2,300[/TD]
[/TR]
[TR]
[TD]Made Value USA Yellow $3,600[/TD]
[/TR]
[TR]
[TD]Made Value USA Orange $5,200[/TD]
[/TR]
</tbody>[/TABLE]