Ranking Unique Pairs with Duplicate Data

[navigator01]

Hello everyone,

I'm new to the forum, but I look forward to contributing to other peoples questions in the future!

I currently have a question involving a scenario where I want to rank pairs of data while ignoring any duplicates in the data. What I am working with is located below. Column A is one item, column B is another item. Together, each row makes up one pair, IE: in row 1 RJET and XPO are one pair. Column C represents a score I have formulated for each pair and they are ranked according to this score.

However, the data in both column A and B is mutually exclusive, IE: Because RJET and XPO is the first pair, XPO can not be in any other pair that has a lower score (column C). What I would like to accomplish is a way to rank the pairs based on column C, but to do so in a way that each item is only used once. For example, in the data below the pairs should be ranked: 1) RJET & XPO, 2) SKYW & EAC etc... so that the pairs in between the two "unique" pairs that use a value that has already been used are ignored.

[TABLE="width: 256"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]A[/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"]C[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD]RJET[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.833939[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD]FSS[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.805035[/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD]VSEC[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.785968[/TD]
[/TR]
[TR]
[TD="class: xl65"]4[/TD]
[TD]ASGN[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.765441[/TD]
[/TR]
[TR]
[TD="class: xl65"]5[/TD]
[TD]SKYW[/TD]
[TD]EAC[/TD]
[TD="align: right"]2.763877[/TD]
[/TR]
[TR]
[TD="class: xl65"]6[/TD]
[TD]RJET[/TD]
[TD]EAC[/TD]
[TD="align: right"]2.641551[/TD]
[/TR]
[TR]
[TD="class: xl65"]7[/TD]
[TD]ABM[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.617283[/TD]
[/TR]
[TR]
[TD="class: xl65"]8[/TD]
[TD]SKYW[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.610225[/TD]
[/TR]
[TR]
[TD="class: xl65"]9[/TD]
[TD]FC[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.599589[/TD]
[/TR]
[TR]
[TD="class: xl65"]10[/TD]
[TD]FIX[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.587622[/TD]
[/TR]
[TR]
[TD="class: xl65"]11[/TD]
[TD]UFPI[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.444511[/TD]
[/TR]
[TR]
[TD="class: xl65"]12[/TD]
[TD]PRSC[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.423591[/TD]
[/TR]
[TR]
[TD="class: xl65"]13[/TD]
[TD]LABL[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.40627[/TD]
[/TR]
[TR]
[TD="class: xl65"]14[/TD]
[TD]KAI[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.393814[/TD]
[/TR]
[TR]
[TD="class: xl65"]15[/TD]
[TD]CRRC[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.390866[/TD]
[/TR]
</tbody>[/TABLE]


Thank you in advance for any help I might receive - I am really stuck on this one!

 

[navigator01]

Dave & Eric - I apologize for the late reply, I have been out of town until today. Thank you both so much for your help!

Eric - Dave said there are some very clever formula guys on this forum, he is definitely right! Your set up works perfect! Very very clever stuff, thank you so much!!

Dave, the new macro seems to be working, the previous issue seems to be resolved! However, the macro is only generating around 15 pairs, I think there should be around 130! I really appreciate your work on this, what you are doing is way above my head!

Again, thanks so much guys. I am blown away how generous you have both been with your time - your help is invaluable! Thank you!!

 

[Eric W]

Glad we could help!

 

[NdNoviceHlp]

Thanks for posting your outcome. I'm not certain why all the pairs weren't identified with the code... must be some logic problem. Anyways, I'm glad that U have a solution that works. Dave