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!

 

[NdNoviceHlp]

I really don't understand your outcome needs? It seems that the only outcome above is "1) RJET & XPO, 2) SKYW & EAC" ? Maybe a bit more info is needed. Dave

 

[navigator01]

Hey Dave,

Thanks for your response - I apologize for not being very clear. The outcome you mentioned above is the one that I desire, however the worksheet I am using contains ~16000 rows of data (or different pairs). I was hoping to find a way to pick out the top "unique" pairs out of all of that data without having to sort through and select them one by one.

For a little more context in the overall project, column A and B represent different stock tickers and I am trying to find the best combinations (or pairs) of stocks. Column C represents the score for each pair of stocks. I essentially had a set of approximately 130 stocks and created every possible combination of two stocks and assigned each pair a score. The reason a stock can only be used once is that, for instance, if I buy stocks RJET and XPO in the #1) RJET & XPO pair, I will have already bought those stocks and will not want to buy them again, because together they are the best possible pair. I would not want to buy pair #2) FSS & XPO after buying pair #1 because I would have already bought XPO, which was a better pair with RJET. Instead I would want to buy pair #5) SKYW & EAC.

I was hoping to find a way to rank a list of these unique pairs like #1) RJET & XPO and #5) SKYW & EAC while ignoring (or otherwise getting rid of) the pairs in between the two as pairs #2 - #4 contain a stock that is best used in combination #1.

Thanks!

 

[NdNoviceHlp]

My take is that there can never be duplicate pairs (ie. RJET & XPO will never occur as a pair twice with different values), U only want to purchase each stock once (ie. once U purchase RJET & XPO U will not purchase either RJET or XPO again) and U want the pairs ranked from highest to lowest based on there values in "C". Do U want a spreadsheet or VBA(macro) solution. VBA may be the only solution however there are some pretty clever spreadsheet formula guys here. Where do U want the outcome? Seems doable. Dave

 

[navigator01]

Dave,

Thanks - that's a much better way of articulating it. You're right, there will never be duplicate pairs but I only want to purchase each stock once. Either a spreadsheet or a VBA solution would be awesome! The outcome/output can be anywhere, no restraints there.

Thanks so much!

 

[NdNoviceHlp]

Give this a trial. Dave

Option Explicit
Private Sub SortUniquePairs()
Dim Cnt As Integer, Cnt2 As Integer, Cnt3 As Integer
Dim LastRow As Integer, Large As Double, Rcnt As Integer
'sorts unique "B"
'output to "D" "E" & "F"
'change sheet name to suit
With Sheets("Sheet1")
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
For Cnt2 = 1 To LastRow
'exclude blank cells in the search
'If .Range("C" & Cnt2).Value <> vbNullString Then
For Cnt3 = (Cnt2 + 1) To LastRow
If .Range("B" & Cnt3).Value = .Range("B" & Cnt2).Value Then
GoTo bart
End If
Next Cnt3
'gets highest value for "B"
Rcnt = Cnt2
Large = .Range("C" & Cnt2).Value
For Cnt3 = 1 To LastRow
If .Range("B" & Cnt2).Value = .Range("B" & Cnt3).Value Then
If .Range("C" & Cnt3).Value > Large Then
Large = .Range("C" & Cnt3).Value
Rcnt = Cnt3
End If
End If
Next Cnt3
'add unique value to output
Cnt = Cnt + 1
.Range("D" & Cnt).Value = .Range("A" & Rcnt).Value
.Range("E" & Cnt).Value = .Range("B" & Rcnt).Value
.Range("F" & Cnt).Value = .Range("C" & Rcnt).Value
'End If
bart:
Next Cnt2
'rank order resuls
Range("D1:F" & Cnt).Sort Key1:=.Range("F1"), Order1:=xlDescending
End With
End Sub

 

[navigator01]

Wow, Dave - Thank you! I really am blown away by your generosity in helping me out with this. This is great stuff and looks like it took some time to put together, so thank you so much.

One thing I did notice is that while the code removes all of the duplicate values in column B, there are still duplicates from column A. Here is the output after running the macro:

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]RJET[/TD]
[TD="width: 64"]XPO[/TD]
[TD="width: 64, align: right"]2.833939[/TD]
[/TR]
[TR]
[TD]SKYW[/TD]
[TD]EAC[/TD]
[TD="align: right"]2.763877[/TD]
[/TR]
[TR]
[TD]KBALB[/TD]
[TD]KEYW[/TD]
[TD="align: right"]1.982458[/TD]
[/TR]
[TR]
[TD]RJET[/TD]
[TD]NPO[/TD]
[TD="align: right"]1.92518[/TD]
[/TR]
[TR]
[TD]SKYW[/TD]
[TD]SNHY[/TD]
[TD="align: right"]1.914606[/TD]
[/TR]
[TR]
[TD]RJET[/TD]
[TD]GY[/TD]
[TD="align: right"]1.854796[/TD]
[/TR]
[TR]
[TD]VSEC[/TD]
[TD]XONE[/TD]
[TD="align: right"]1.844602[/TD]
[/TR]
[TR]
[TD]SKYW[/TD]
[TD]DGI[/TD]
[TD="align: right"]1.843945[/TD]
[/TR]
[TR]
[TD]SKYW[/TD]
[TD]SWFT[/TD]
[TD="align: right"]1.771905[/TD]
[/TR]
[TR]
[TD]KBALB[/TD]
[TD]CUB[/TD]
[TD="align: right"]1.76878[/TD]
[/TR]
[TR]
[TD]RJET[/TD]
[TD]SAAS[/TD]
[TD="align: right"]1.764941[/TD]
[/TR]
[TR]
[TD]FSS[/TD]
[TD]ATU[/TD]
[TD="align: right"]1.734972[/TD]
[/TR]
[TR]
[TD]FSS[/TD]
[TD]WAGE[/TD]
[TD="align: right"]1.733235[/TD]
[/TR]
[TR]
[TD]FSS[/TD]
[TD]LOCK[/TD]
[TD="align: right"]1.722323[/TD]
[/TR]
[TR]
[TD]SKYW[/TD]
[TD]EME[/TD]
[TD="align: right"]1.645214[/TD]
[/TR]
[TR]
[TD]SKYW[/TD]
[TD]STNG[/TD]
[TD="align: right"]1.644388[/TD]
[/TR]
[TR]
[TD]FSS[/TD]
[TD]ULTI[/TD]
[TD="align: right"]1.636401[/TD]
[/TR]
[TR]
[TD]RJET[/TD]
[TD]ISSC[/TD]
[TD="align: right"]1.633952[/TD]
[/TR]
[TR]
[TD]FSS[/TD]
[TD]AYI[/TD]
[TD="align: right"]1.627569[/TD]
[/TR]
[TR]
[TD]ACCO[/TD]
[TD]NAT[/TD]
[TD="align: right"]1.612394[/TD]
[/TR]
[TR]
[TD]RJET[/TD]
[TD]ECHO[/TD]
[TD="align: right"]1.593331[/TD]
[/TR]
[TR]
[TD]SKYW[/TD]
[TD]GVA[/TD]
[TD="align: right"]1.557466[/TD]
[/TR]
[TR]
[TD]SKYW[/TD]
[TD]PRLB[/TD]
[TD="align: right"]1.546574[/TD]
[/TR]
[TR]
[TD]SKYW[/TD]
[TD]PRIM[/TD]
[TD="align: right"]1.538642[/TD]
[/TR]
[TR]
[TD]SKYW[/TD]
[TD]ORB[/TD]
[TD="align: right"]1.518543[/TD]
[/TR]
[TR]
[TD]SKYW[/TD]
[TD]AIN[/TD]
[TD="align: right"]1.518532[/TD]
[/TR]
[TR]
[TD]SKYW[/TD]
[TD]AIT[/TD]
[TD="align: right"]1.513101[/TD]
[/TR]
</tbody>[/TABLE]


Ideally once a stock in either column A or B is purchased it would be excluded, it seems that this might be only happening for column B now. I have messed around with the code in VBA a bit and haven't been able to find a solution yet, do you have any guidance? I included more rows of data below if that would at all be helpful.

Again, thank you so much for your help. You have been extremely generous and you have my word that I will pay this forward on the forum!



[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]RJET[/TD]
[TD="width: 64"]XPO[/TD]
[TD="width: 64, align: right"]2.833939[/TD]
[/TR]
[TR]
[TD]FSS[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.805035[/TD]
[/TR]
[TR]
[TD]VSEC[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.785968[/TD]
[/TR]
[TR]
[TD]ASGN[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.765441[/TD]
[/TR]
[TR]
[TD]SKYW[/TD]
[TD]EAC[/TD]
[TD="align: right"]2.763877[/TD]
[/TR]
[TR]
[TD]RJET[/TD]
[TD]EAC[/TD]
[TD="align: right"]2.641551[/TD]
[/TR]
[TR]
[TD]ABM[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.617283[/TD]
[/TR]
[TR]
[TD]SKYW[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.610225[/TD]
[/TR]
[TR]
[TD]FC[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.599589[/TD]
[/TR]
[TR]
[TD]FIX[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.587622[/TD]
[/TR]
[TR]
[TD]UFPI[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.444511[/TD]
[/TR]
[TR]
[TD]PRSC[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.423591[/TD]
[/TR]
[TR]
[TD]LABL[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.40627[/TD]
[/TR]
[TR]
[TD]KAI[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.393814[/TD]
[/TR]
[TR]
[TD]CRRC[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.390866[/TD]
[/TR]
[TR]
[TD]CMCO[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.389256[/TD]
[/TR]
[TR]
[TD]MWA[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.384641[/TD]
[/TR]
[TR]
[TD]GNRC[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.376736[/TD]
[/TR]
[TR]
[TD]MTZ[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.359929[/TD]
[/TR]
[TR]
[TD]NCI[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.354649[/TD]
[/TR]
[TR]
[TD]POWL[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.351528[/TD]
[/TR]
[TR]
[TD]TBI[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.330272[/TD]
[/TR]
[TR]
[TD]SAIA[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.328209[/TD]
[/TR]
[TR]
[TD]USTR[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.324989[/TD]
[/TR]
[TR]
[TD]ASGN[/TD]
[TD]EAC[/TD]
[TD="align: right"]2.30931[/TD]
[/TR]
[TR]
[TD]APOG[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.301142[/TD]
[/TR]
[TR]
[TD]BCO[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.299756[/TD]
[/TR]
[TR]
[TD]HA[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.29064[/TD]
[/TR]
[TR]
[TD]KBALB[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.270594[/TD]
[/TR]
[TR]
[TD]PGTI[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.265024[/TD]
[/TR]
[TR]
[TD]CEB[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.262908[/TD]
[/TR]
[TR]
[TD]UACL[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.258017[/TD]
[/TR]
[TR]
[TD]MSA[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.249153[/TD]
[/TR]
[TR]
[TD]EGL[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.242668[/TD]
[/TR]
[TR]
[TD]HNI[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.24118[/TD]
[/TR]
[TR]
[TD]SPB[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.235594[/TD]
[/TR]
[TR]
[TD]MTZ[/TD]
[TD]EAC[/TD]
[TD="align: right"]2.230917[/TD]
[/TR]
[TR]
[TD]ALGT[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.216751[/TD]
[/TR]
[TR]
[TD]HCKT[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.213907[/TD]
[/TR]
[TR]
[TD]VSEC[/TD]
[TD]EAC[/TD]
[TD="align: right"]2.211258[/TD]
[/TR]
[TR]
[TD]GK[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.206138[/TD]
[/TR]
[TR]
[TD]GFF[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.20174[/TD]
[/TR]
[TR]
[TD]SAIA[/TD]
[TD]EAC[/TD]
[TD="align: right"]2.191742[/TD]
[/TR]
[TR]
[TD]ISH[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.189687[/TD]
[/TR]
[TR]
[TD]FSS[/TD]
[TD]EAC[/TD]
[TD="align: right"]2.185216[/TD]
[/TR]
[TR]
[TD]CIR[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.183912[/TD]
[/TR]
[TR]
[TD]WSO[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.180773[/TD]
[/TR]
[TR]
[TD]SCS[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.179946[/TD]
[/TR]
[TR]
[TD]QUAD[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.17326[/TD]
[/TR]
[TR]
[TD]DLX[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.172244[/TD]
[/TR]
[TR]
[TD]FIX[/TD]
[TD]EAC[/TD]
[TD="align: right"]2.168338[/TD]
[/TR]
[TR]
[TD]ALG[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.164698[/TD]
[/TR]
[TR]
[TD]NWPX[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.164064[/TD]
[/TR]
[TR]
[TD]IIIN[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.16331[/TD]
[/TR]
[TR]
[TD]ICFI[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.151547[/TD]
[/TR]
[TR]
[TD]CGX[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.146141[/TD]
[/TR]
[TR]
[TD]IIIN[/TD]
[TD]EAC[/TD]
[TD="align: right"]2.140881[/TD]
[/TR]
[TR]
[TD]VPRT[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.139588[/TD]
[/TR]
[TR]
[TD]FC[/TD]
[TD]EAC[/TD]
[TD="align: right"]2.131737[/TD]
[/TR]
[TR]
[TD]EBF[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.123107[/TD]
[/TR]
[TR]
[TD]OTTR[/TD]
[TD]EAC[/TD]
[TD="align: right"]2.116559[/TD]
[/TR]
[TR]
[TD]UACL[/TD]
[TD]EAC[/TD]
[TD="align: right"]2.116437[/TD]
[/TR]
[TR]
[TD]UNF[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.114694[/TD]
[/TR]
[TR]
[TD]PACR[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.112746[/TD]
[/TR]
[TR]
[TD]ARII[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.111949[/TD]
[/TR]
[TR]
[TD]HNI[/TD]
[TD]EAC[/TD]
[TD="align: right"]2.109716[/TD]
[/TR]
[TR]
[TD]PKOH[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.109688[/TD]
[/TR]
[TR]
[TD]DCO[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.109601[/TD]
[/TR]
[TR]
[TD]RXN[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.107999[/TD]
[/TR]
[TR]
[TD]WIRE[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.107751[/TD]
[/TR]
[TR]
[TD]MNTX[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.106761[/TD]
[/TR]
[TR]
[TD]PRSC[/TD]
[TD]EAC[/TD]
[TD="align: right"]2.094465[/TD]
[/TR]
[TR]
[TD]MWA[/TD]
[TD]EAC[/TD]
[TD="align: right"]2.088072[/TD]
[/TR]
[TR]
[TD]ABM[/TD]
[TD]EAC[/TD]
[TD="align: right"]2.07839[/TD]
[/TR]
[TR]
[TD]OTTR[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.076723[/TD]
[/TR]
[TR]
[TD]KFRC[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.076398[/TD]
[/TR]
[TR]
[TD]SWHC[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.073804[/TD]
[/TR]
[TR]
[TD]AIMC[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.061114[/TD]
[/TR]
[TR]
[TD]ALGT[/TD]
[TD]EAC[/TD]
[TD="align: right"]2.060652[/TD]
[/TR]
[TR]
[TD]CW[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.059293[/TD]
[/TR]
[TR]
[TD]BLDR[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.059255[/TD]
[/TR]
[TR]
[TD]MOG.A[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.058032[/TD]
[/TR]
[TR]
[TD]SPB[/TD]
[TD]EAC[/TD]
[TD="align: right"]2.047214[/TD]
[/TR]
[TR]
[TD]PIKE[/TD]
[TD]EAC[/TD]
[TD="align: right"]2.045063[/TD]
[/TR]
[TR]
[TD]HDNG[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.044578[/TD]
[/TR]
[TR]
[TD]ECOL[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.042379[/TD]
[/TR]
[TR]
[TD]DXPE[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.040843[/TD]
[/TR]
[TR]
[TD]WNC[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.038954[/TD]
[/TR]
[TR]
[TD]XRM[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.031008[/TD]
[/TR]
[TR]
[TD]TPC[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.027007[/TD]
[/TR]
[TR]
[TD]LABL[/TD]
[TD]EAC[/TD]
[TD="align: right"]2.025659[/TD]
[/TR]
[TR]
[TD]BBSI[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.023795[/TD]
[/TR]
[TR]
[TD]APOG[/TD]
[TD]EAC[/TD]
[TD="align: right"]2.014695[/TD]
[/TR]
[TR]
[TD]KFY[/TD]
[TD]XPO[/TD]
[TD="align: right"]2.013276[/TD]
[/TR]
[TR]
[TD]PATR[/TD]
[TD]XPO[/TD]
[TD="align: right"]1.995858[/TD]
[/TR]
[TR]
[TD]AIMC[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.991982[/TD]
[/TR]
[TR]
[TD]MLI[/TD]
[TD]XPO[/TD]
[TD="align: right"]1.989521[/TD]
[/TR]
[TR]
[TD]HURN[/TD]
[TD]XPO[/TD]
[TD="align: right"]1.98886[/TD]
[/TR]
[TR]
[TD]TBI[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.983713[/TD]
[/TR]
[TR]
[TD]KBALB[/TD]
[TD]KEYW[/TD]
[TD="align: right"]1.982458[/TD]
[/TR]
[TR]
[TD]CRRC[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.979702[/TD]
[/TR]
[TR]
[TD]JBLU[/TD]
[TD]XPO[/TD]
[TD="align: right"]1.977941[/TD]
[/TR]
[TR]
[TD]PGTI[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.977305[/TD]
[/TR]
[TR]
[TD]FSTR[/TD]
[TD]XPO[/TD]
[TD="align: right"]1.973703[/TD]
[/TR]
[TR]
[TD]BLDR[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.972931[/TD]
[/TR]
[TR]
[TD]GNRC[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.969726[/TD]
[/TR]
[TR]
[TD]POWL[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.956923[/TD]
[/TR]
[TR]
[TD]PKOH[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.952256[/TD]
[/TR]
[TR]
[TD]UNF[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.950115[/TD]
[/TR]
[TR]
[TD]BCO[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.942053[/TD]
[/TR]
[TR]
[TD]MYRG[/TD]
[TD]XPO[/TD]
[TD="align: right"]1.939376[/TD]
[/TR]
[TR]
[TD]RJET[/TD]
[TD]KEYW[/TD]
[TD="align: right"]1.936522[/TD]
[/TR]
[TR]
[TD]GFF[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.927417[/TD]
[/TR]
[TR]
[TD]RJET[/TD]
[TD]NPO[/TD]
[TD="align: right"]1.92518[/TD]
[/TR]
[TR]
[TD]USTR[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.921987[/TD]
[/TR]
[TR]
[TD]LYTS[/TD]
[TD]XPO[/TD]
[TD="align: right"]1.918697[/TD]
[/TR]
[TR]
[TD]CMCO[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.914878[/TD]
[/TR]
[TR]
[TD]SKYW[/TD]
[TD]SNHY[/TD]
[TD="align: right"]1.914606[/TD]
[/TR]
[TR]
[TD]MSA[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.911118[/TD]
[/TR]
[TR]
[TD]CEB[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.904488[/TD]
[/TR]
[TR]
[TD]SKYW[/TD]
[TD]KEYW[/TD]
[TD="align: right"]1.904135[/TD]
[/TR]
[TR]
[TD]MATX[/TD]
[TD]XPO[/TD]
[TD="align: right"]1.903457[/TD]
[/TR]
[TR]
[TD]AIR[/TD]
[TD]XPO[/TD]
[TD="align: right"]1.902878[/TD]
[/TR]
[TR]
[TD]CGX[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.883541[/TD]
[/TR]
[TR]
[TD]GSOL[/TD]
[TD]XPO[/TD]
[TD="align: right"]1.876326[/TD]
[/TR]
[TR]
[TD]DLX[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.875558[/TD]
[/TR]
[TR]
[TD]ROCK[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.868998[/TD]
[/TR]
[TR]
[TD]SCS[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.860357[/TD]
[/TR]
[TR]
[TD]RJET[/TD]
[TD]GY[/TD]
[TD="align: right"]1.854796[/TD]
[/TR]
[TR]
[TD]PIKE[/TD]
[TD]XPO[/TD]
[TD="align: right"]1.848365[/TD]
[/TR]
[TR]
[TD]KAI[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.848202[/TD]
[/TR]
[TR]
[TD]VSEC[/TD]
[TD]XONE[/TD]
[TD="align: right"]1.844602[/TD]
[/TR]
[TR]
[TD]SKYW[/TD]
[TD]DGI[/TD]
[TD="align: right"]1.843945[/TD]
[/TR]
[TR]
[TD]XRM[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.837255[/TD]
[/TR]
[TR]
[TD]GK[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.83607[/TD]
[/TR]
[TR]
[TD]ARII[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.83095[/TD]
[/TR]
[TR]
[TD]BRSS[/TD]
[TD]XPO[/TD]
[TD="align: right"]1.829337[/TD]
[/TR]
[TR]
[TD]SKYW[/TD]
[TD]NPO[/TD]
[TD="align: right"]1.828435[/TD]
[/TR]
[TR]
[TD]FSTR[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.821324[/TD]
[/TR]
[TR]
[TD]SKYW[/TD]
[TD]GY[/TD]
[TD="align: right"]1.813646[/TD]
[/TR]
[TR]
[TD]VVI[/TD]
[TD]XPO[/TD]
[TD="align: right"]1.810509[/TD]
[/TR]
[TR]
[TD]RUSHA[/TD]
[TD]XPO[/TD]
[TD="align: right"]1.805398[/TD]
[/TR]
[TR]
[TD]HDNG[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.797803[/TD]
[/TR]
[TR]
[TD]UFPI[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.786421[/TD]
[/TR]
[TR]
[TD]NTK[/TD]
[TD]XPO[/TD]
[TD="align: right"]1.780579[/TD]
[/TR]
[TR]
[TD]WNC[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.776963[/TD]
[/TR]
[TR]
[TD]SKYW[/TD]
[TD]SWFT[/TD]
[TD="align: right"]1.771905[/TD]
[/TR]
[TR]
[TD]RUSHA[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.770475[/TD]
[/TR]
[TR]
[TD]KBALB[/TD]
[TD]CUB[/TD]
[TD="align: right"]1.76878[/TD]
[/TR]
[TR]
[TD]JBLU[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.766136[/TD]
[/TR]
[TR]
[TD]RJET[/TD]
[TD]SAAS[/TD]
[TD="align: right"]1.764941[/TD]
[/TR]
[TR]
[TD]BOOM[/TD]
[TD]XPO[/TD]
[TD="align: right"]1.764669[/TD]
[/TR]
[TR]
[TD]ICFI[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.760892[/TD]
[/TR]
[TR]
[TD]WSO[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.748074[/TD]
[/TR]
[TR]
[TD]DCO[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.74147[/TD]
[/TR]
[TR]
[TD]QUAD[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.739313[/TD]
[/TR]
[TR]
[TD]ASGN[/TD]
[TD]NPO[/TD]
[TD="align: right"]1.739061[/TD]
[/TR]
[TR]
[TD]ALG[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.738797[/TD]
[/TR]
[TR]
[TD]NWPX[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.736562[/TD]
[/TR]
[TR]
[TD]FSS[/TD]
[TD]ATU[/TD]
[TD="align: right"]1.734972[/TD]
[/TR]
[TR]
[TD]ASGN[/TD]
[TD]GY[/TD]
[TD="align: right"]1.734165[/TD]
[/TR]
[TR]
[TD]FSS[/TD]
[TD]WAGE[/TD]
[TD="align: right"]1.733235[/TD]
[/TR]
[TR]
[TD]NCI[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.731301[/TD]
[/TR]
[TR]
[TD]PACR[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.728112[/TD]
[/TR]
[TR]
[TD]ASGN[/TD]
[TD]KEYW[/TD]
[TD="align: right"]1.726129[/TD]
[/TR]
[TR]
[TD]FSS[/TD]
[TD]LOCK[/TD]
[TD="align: right"]1.722323[/TD]
[/TR]
[TR]
[TD]CIR[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.722058[/TD]
[/TR]
[TR]
[TD]PIKE[/TD]
[TD]KEYW[/TD]
[TD="align: right"]1.721236[/TD]
[/TR]
[TR]
[TD]ECOL[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.721007[/TD]
[/TR]
[TR]
[TD]RJET[/TD]
[TD]ATU[/TD]
[TD="align: right"]1.715298[/TD]
[/TR]
[TR]
[TD]ULTR[/TD]
[TD]XPO[/TD]
[TD="align: right"]1.714285[/TD]
[/TR]
[TR]
[TD]RJET[/TD]
[TD]SWFT[/TD]
[TD="align: right"]1.708069[/TD]
[/TR]
[TR]
[TD]RJET[/TD]
[TD]DGI[/TD]
[TD="align: right"]1.703565[/TD]
[/TR]
[TR]
[TD]BBSI[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.702937[/TD]
[/TR]
[TR]
[TD]FSS[/TD]
[TD]NPO[/TD]
[TD="align: right"]1.700255[/TD]
[/TR]
[TR]
[TD]MNTX[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.695826[/TD]
[/TR]
[TR]
[TD]FSS[/TD]
[TD]GY[/TD]
[TD="align: right"]1.694567[/TD]
[/TR]
[TR]
[TD]SKYW[/TD]
[TD]LOCK[/TD]
[TD="align: right"]1.690406[/TD]
[/TR]
[TR]
[TD]TPC[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.689735[/TD]
[/TR]
[TR]
[TD]ASGN[/TD]
[TD]ATU[/TD]
[TD="align: right"]1.683518[/TD]
[/TR]
[TR]
[TD]OTTR[/TD]
[TD]KEYW[/TD]
[TD="align: right"]1.681653[/TD]
[/TR]
[TR]
[TD]VSEC[/TD]
[TD]NPO[/TD]
[TD="align: right"]1.68158[/TD]
[/TR]
[TR]
[TD]SKYW[/TD]
[TD]WAGE[/TD]
[TD="align: right"]1.680104[/TD]
[/TR]
[TR]
[TD]MTZ[/TD]
[TD]KEYW[/TD]
[TD="align: right"]1.679199[/TD]
[/TR]
[TR]
[TD]FSS[/TD]
[TD]SWFT[/TD]
[TD="align: right"]1.670881[/TD]
[/TR]
[TR]
[TD]VSEC[/TD]
[TD]ATU[/TD]
[TD="align: right"]1.667034[/TD]
[/TR]
[TR]
[TD]ULTR[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.665785[/TD]
[/TR]
[TR]
[TD]SKYW[/TD]
[TD]ATU[/TD]
[TD="align: right"]1.664102[/TD]
[/TR]
[TR]
[TD]VSEC[/TD]
[TD]SWFT[/TD]
[TD="align: right"]1.660736[/TD]
[/TR]
[TR]
[TD]SKYW[/TD]
[TD]SAAS[/TD]
[TD="align: right"]1.660274[/TD]
[/TR]
[TR]
[TD]VSEC[/TD]
[TD]CUB[/TD]
[TD="align: right"]1.659365[/TD]
[/TR]
[TR]
[TD]NTK[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.658132[/TD]
[/TR]
[TR]
[TD]AEGN[/TD]
[TD]XPO[/TD]
[TD="align: right"]1.655248[/TD]
[/TR]
[TR]
[TD]FC[/TD]
[TD]KEYW[/TD]
[TD="align: right"]1.651474[/TD]
[/TR]
[TR]
[TD]VVI[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.648932[/TD]
[/TR]
[TR]
[TD]MYRG[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.647977[/TD]
[/TR]
[TR]
[TD]VSEC[/TD]
[TD]WAGE[/TD]
[TD="align: right"]1.646805[/TD]
[/TR]
[TR]
[TD]SKYW[/TD]
[TD]EME[/TD]
[TD="align: right"]1.645214[/TD]
[/TR]
[TR]
[TD]SKYW[/TD]
[TD]STNG[/TD]
[TD="align: right"]1.644388[/TD]
[/TR]
[TR]
[TD]BLDR[/TD]
[TD]KEYW[/TD]
[TD="align: right"]1.643799[/TD]
[/TR]
[TR]
[TD]TWIN[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.642074[/TD]
[/TR]
[TR]
[TD]ASGN[/TD]
[TD]DGI[/TD]
[TD="align: right"]1.640225[/TD]
[/TR]
[TR]
[TD]FSS[/TD]
[TD]DGI[/TD]
[TD="align: right"]1.637768[/TD]
[/TR]
[TR]
[TD]FSS[/TD]
[TD]ULTI[/TD]
[TD="align: right"]1.636401[/TD]
[/TR]
[TR]
[TD]FC[/TD]
[TD]DGI[/TD]
[TD="align: right"]1.634745[/TD]
[/TR]
[TR]
[TD]RJET[/TD]
[TD]ISSC[/TD]
[TD="align: right"]1.633952[/TD]
[/TR]
[TR]
[TD]ENOC[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.63071[/TD]
[/TR]
[TR]
[TD]RJET[/TD]
[TD]STNG[/TD]
[TD="align: right"]1.6302[/TD]
[/TR]
[TR]
[TD]FSS[/TD]
[TD]AYI[/TD]
[TD="align: right"]1.627569[/TD]
[/TR]
[TR]
[TD]FSS[/TD]
[TD]SNHY[/TD]
[TD="align: right"]1.627023[/TD]
[/TR]
[TR]
[TD]FC[/TD]
[TD]GY[/TD]
[TD="align: right"]1.626582[/TD]
[/TR]
[TR]
[TD]VPRT[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.624705[/TD]
[/TR]
[TR]
[TD]FSS[/TD]
[TD]CUB[/TD]
[TD="align: right"]1.623646[/TD]
[/TR]
[TR]
[TD]VSEC[/TD]
[TD]GY[/TD]
[TD="align: right"]1.621856[/TD]
[/TR]
[TR]
[TD]SKYW[/TD]
[TD]ISSC[/TD]
[TD="align: right"]1.618656[/TD]
[/TR]
[TR]
[TD]ASGN[/TD]
[TD]WAGE[/TD]
[TD="align: right"]1.618422[/TD]
[/TR]
[TR]
[TD]FIX[/TD]
[TD]SNHY[/TD]
[TD="align: right"]1.615855[/TD]
[/TR]
[TR]
[TD]ACCO[/TD]
[TD]NAT[/TD]
[TD="align: right"]1.612394[/TD]
[/TR]
[TR]
[TD]MOG.A[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.607425[/TD]
[/TR]
[TR]
[TD]APOG[/TD]
[TD]KEYW[/TD]
[TD="align: right"]1.60305[/TD]
[/TR]
[TR]
[TD]FC[/TD]
[TD]LOCK[/TD]
[TD="align: right"]1.602422[/TD]
[/TR]
[TR]
[TD]RJET[/TD]
[TD]SNHY[/TD]
[TD="align: right"]1.600884[/TD]
[/TR]
[TR]
[TD]SKYW[/TD]
[TD]AYI[/TD]
[TD="align: right"]1.600819[/TD]
[/TR]
[TR]
[TD]FIX[/TD]
[TD]SWFT[/TD]
[TD="align: right"]1.598871[/TD]
[/TR]
[TR]
[TD]ASGN[/TD]
[TD]LOCK[/TD]
[TD="align: right"]1.595357[/TD]
[/TR]
[TR]
[TD]MLI[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.594709[/TD]
[/TR]
[TR]
[TD]RJET[/TD]
[TD]ECHO[/TD]
[TD="align: right"]1.593331[/TD]
[/TR]
[TR]
[TD]LYTS[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.589621[/TD]
[/TR]
[TR]
[TD]FIX[/TD]
[TD]LOCK[/TD]
[TD="align: right"]1.58838[/TD]
[/TR]
[TR]
[TD]FIX[/TD]
[TD]WAGE[/TD]
[TD="align: right"]1.584517[/TD]
[/TR]
[TR]
[TD]NTK[/TD]
[TD]KEYW[/TD]
[TD="align: right"]1.584298[/TD]
[/TR]
[TR]
[TD]EBF[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.578296[/TD]
[/TR]
[TR]
[TD]FC[/TD]
[TD]WAGE[/TD]
[TD="align: right"]1.576843[/TD]
[/TR]
[TR]
[TD]ENOC[/TD]
[TD]XPO[/TD]
[TD="align: right"]1.576682[/TD]
[/TR]
[TR]
[TD]FC[/TD]
[TD]ATU[/TD]
[TD="align: right"]1.575383[/TD]
[/TR]
[TR]
[TD]RJET[/TD]
[TD]EME[/TD]
[TD="align: right"]1.57297[/TD]
[/TR]
[TR]
[TD]ASGN[/TD]
[TD]SWFT[/TD]
[TD="align: right"]1.571777[/TD]
[/TR]
[TR]
[TD]FIX[/TD]
[TD]ATU[/TD]
[TD="align: right"]1.57144[/TD]
[/TR]
[TR]
[TD]FC[/TD]
[TD]NPO[/TD]
[TD="align: right"]1.570936[/TD]
[/TR]
[TR]
[TD]KBALB[/TD]
[TD]DGI[/TD]
[TD="align: right"]1.569923[/TD]
[/TR]
[TR]
[TD]ASGN[/TD]
[TD]ULTI[/TD]
[TD="align: right"]1.569147[/TD]
[/TR]
[TR]
[TD]HURN[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.56654[/TD]
[/TR]
[TR]
[TD]FIX[/TD]
[TD]NPO[/TD]
[TD="align: right"]1.564877[/TD]
[/TR]
[TR]
[TD]KFY[/TD]
[TD]EAC[/TD]
[TD="align: right"]1.563332[/TD]
[/TR]
[TR]
[TD]FSS[/TD]
[TD]STNG[/TD]
[TD="align: right"]1.562396[/TD]
[/TR]
[TR]
[TD]ASGN[/TD]
[TD]XONE[/TD]
[TD="align: right"]1.56058[/TD]
[/TR]
[TR]
[TD]FIX[/TD]
[TD]GY[/TD]
[TD="align: right"]1.559944[/TD]
[/TR]
[TR]
[TD]SKYW[/TD]
[TD]GVA[/TD]
[TD="align: right"]1.557466[/TD]
[/TR]
[TR]
[TD]SPB[/TD]
[TD]KEYW[/TD]
[TD="align: right"]1.553933[/TD]
[/TR]
[TR]
[TD]ROCK[/TD]
[TD]XPO[/TD]
[TD="align: right"]1.546865[/TD]
[/TR]
</tbody>[/TABLE]

 

[navigator01]

To clarify my previous post, the output I pasted is only a partial of what the macro generated.

Thanks!

 

[Eric W]

Here is a formula version that might work for you:

	A	B	C	D	E	F	G
RJET	XPO	RJET	XPO
FSS	XPO	SKYW	EAC
VSEC	XPO
ASGN	XPO
SKYW	EAC
RJET	EAC
ABM	XPO
SKYW	XPO
FC	XPO
FIX	XPO
UFPI	XPO
PRSC	XPO
LABL	XPO
KAI	XPO
CRRC	XPO

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]2.833939[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]2.833939[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]2.805035[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]2.763877[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2.785968[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]2.765441[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]2.763877[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]2.641551[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]2.617283[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]2.610225[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]2.599589[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]2.587622[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]2.444511[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]2.423591[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]2.40627[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]2.393814[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]2.390866[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E1[/TH]
[TD="align: left"]=A1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F1[/TH]
[TD="align: left"]=B1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G1[/TH]
[TD="align: left"]=C1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=IF($D2<>"",INDEX(A:A,$D2))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]{=IF(D1="","",IFERROR(SMALL(IF(ISERROR(MATCH($A$2:$A$15,$E$1:$E1,0))*ISERROR(MATCH($B$2:$B$15,$F$1:$F1,0)),ROW($A$2:$A$15)),1),""))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



This assumes that they are sorted by column C. This implies that the first row will always be included, so D1:G1 are essentially just copied over. The D2 formula finds the next valid row. It is an array formula, so confirm with Control-Shift-Enter. Then you can enter the E2 formula, and copy it to F2:G2. Then copy D2:G2 and paste that down the columns as far as needed.

These formulas also assume that no stock in column A will show up in B, and vice versa. If that's the case, then I can adapt the formulas. Let me know.

I also wonder about the logic behind your method. Consider:
A & B rated 10
A & C rated 9
B & D rated 8

Based on your logic, A & B would be taken for a total of 10, but A&C and B&D would be excluded for a total of 17. Figuring out all the combinations could be a much trickier proposition.

 

[NdNoviceHlp]

U can give Eric's spreadsheet solution a trail.... seems like it should work. Here's a second attempt. If it works I'll tidy it up, Dave

Option Explicit
Private Sub SortUniquePairs()
Dim Cnt As Integer, Cnt2 As Integer, Cnt3 As Integer
Dim LastRow As Integer, Large As Double, Rcnt As Integer
'sorts unique "B"
'output to "G" "H" & "I"
'change sheet name to suit
With Sheets("Sheet1")
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
Sheets("Sheet1").Range("D1:I" & LastRow).ClearContents
For Cnt2 = 1 To LastRow
'exclude blank cells in the search
'If .Range("C" & Cnt2).Value <> vbNullString Then
For Cnt3 = (Cnt2 + 1) To LastRow
If .Range("B" & Cnt3).Value = .Range("B" & Cnt2).Value Then
GoTo bart
End If
Next Cnt3
'gets highest value for "B"
Rcnt = Cnt2
Large = .Range("C" & Cnt2).Value
For Cnt3 = 1 To LastRow
If .Range("B" & Cnt2).Value = .Range("B" & Cnt3).Value Then
If .Range("C" & Cnt3).Value > Large Then
Large = .Range("C" & Cnt3).Value
Rcnt = Cnt3
End If
End If
Next Cnt3
'add unique value to output
Cnt = Cnt + 1
.Range("D" & Cnt).Value = .Range("A" & Rcnt).Value
.Range("E" & Cnt).Value = .Range("B" & Rcnt).Value
.Range("F" & Cnt).Value = .Range("C" & Rcnt).Value
'End If
bart:
Next Cnt2
Cnt = 0
'With Sheets("Sheet1")
LastRow = .Range("D" & .Rows.Count).End(xlUp).Row
For Cnt2 = 1 To LastRow
'exclude blank cells in the search
'If .Range("C" & Cnt2).Value <> vbNullString Then
For Cnt3 = (Cnt2 + 1) To LastRow
If .Range("D" & Cnt3).Value = .Range("D" & Cnt2).Value Then
GoTo bart2
End If
Next Cnt3
'gets highest value for "B"
Rcnt = Cnt2
Large = .Range("F" & Cnt2).Value
For Cnt3 = 1 To LastRow
If .Range("D" & Cnt2).Value = .Range("D" & Cnt3).Value Then
If .Range("F" & Cnt3).Value > Large Then
Large = .Range("F" & Cnt3).Value
Rcnt = Cnt3
End If
End If
Next Cnt3
'add unique value to output
Cnt = Cnt + 1
.Range("G" & Cnt).Value = .Range("D" & Rcnt).Value
.Range("H" & Cnt).Value = .Range("E" & Rcnt).Value
.Range("I" & Cnt).Value = .Range("F" & Rcnt).Value
'End If
bart2:
Next Cnt2
'rank order resuls
Range("G1:I" & Cnt).Sort Key1:=.Range("I1"), Order1:=xlDescending
End With
End Sub