RANK function w/ no duplicates and multiple, SEQUENTIAL criteria

[pthalodezin]

So I have tried a couple methods I found on the internet and nothing seems to give me what I need.

I want to rank a list of names based on a series of criteria (5 to be exact).

The first criteria is a number (high number = higher rank)
The second criteria is a number (low number = higher rank)
The third criteria is a word (open = higher rank, closed = lower rank)
The fourth criteria is a number (low number = higher rank)
The fifth criteria is a number (low number = higher rank).

The part that is hanging me up is that they need to be treated sequentially and each subsequent criteria is only necessary if the values are equal (ie if the values for name1 are equal values 1,2 & 3 for name2 but not 4, then the rank does not address 5)

I would prefer to not have to post the workbook as this is part of a much larger excel file that has some proprietary & private information embedded.

[leelnich]

1) How many digits in each of the numbers?
2) Are they Whole Numbers?
3) Is the word either "open" or "closed"?
4) "No Duplicates"? Do you mean no duplicate NAMES (meaning repeats must be eliminated)? Or no duplicate RANKS?

[pthalodezin]

1) 1 to 4 but most values are either 1 or 2 digits
2) whole number
3) no, but it is a dichotomy (ie not true/false, yes/no, on/off, but a more nuanced dichotomy)
4) I want numbers from 1 to 50 as results, no duplicate rank, no ties

[leelnich]

3) no, but it is a dichotomy (ie not true/false, yes/no, on/off, but a more nuanced dichotomy)

So what does the test for the word look like? Please be precise, it may be necessary to convert the result to a 1 or 0.

[pthalodezin]

One value is good - higher rank, the other value is bad - lower rank. This is a sequential tie breaker formula.

So using the "open" or "closed" nomenclature, if name1 cell in 3rd column is "open" (good number) and name2 cell in 3rd column is "closed" (bad number), name1 would receive the higher rank. If both equal either "open" or "closed", then the 4th criteria must be investigated

[leelnich]

OK, in column H, I concatenated all 5 rankings (in order of priority) into a single "sort key" for each of 49 rows, formatted as text to preserve alignment. Paste this sort key formula in H2 and copy down: (If desired, column can be hidden or moved for aesthetic purposes.)

Please Login or Register  to view this content.

Paste this ranking formula in F2 and copy down:

Please Login or Register  to view this content.

Row\Col	A	B	C	D	E	F	G	H
1	Num1	Num2	Word	Num3	Num4	Rank		SortKey
2	1049	8723	open	1851	8349	41		4139A0941
3	8920	9849	closed	2875	3099	2		0246B1617
4	5415	4693	closed	3303	9919	19		1922B1949
5	7883	8030	open	8543	811	6		0637A4603
6	6420	4594	closed	1726	5939	16		1621B0529
7	2611	5928	closed	1783	2844	31		3126B0713
8	3172	770	closed	7230	4012	29		2905B3520
9	5817	7375	closed	9665	7189	17		1730B4934
10	8417	9896	open	3296	7238	4		0448A1835
11	207	4158	open	2944	7285	48		4817A1736

NOTE: Num1_, Num2_, Word, Num3_, and Num4_ are Named Ranges referring to columns A,B,C,D, and E, rows 2-50

Please click the Add Reputation star below any helpful posts, and use Thread Tools (up top) to mark your thread as SOLVED once you have your answer. Thanks!-Lee

[pthalodezin]

leelnich, now that was creative and it works. Thanks. I would have never thought to concatenate and go with a text string. Brilliant. For those that see this and notice that there are no ties, I have provided a proof (attached) that shows this with smaller numbers, names and a vlookup table that automatically updates as the values in the table change. Once again, great work leelnich. thanks

[leelnich]

OOPs Double post

[leelnich]

I appreciate the praise, glad I could help! If complete, please mark your thread as SOLVED (Thread Tools up top). Thanks -Lee

Please click the Add Reputation star below any helpful posts, and use Thread Tools (up top) to mark your thread as SOLVED once you have your answer. Thanks!-Lee