Ranking Unique Pairs with Duplicate Data

N

navigator01

New Member
Joined
Mar 9, 2016
Messages
6
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.

ABC
1RJETXPO2.833939
2FSSXPO2.805035
3VSECXPO2.785968
4ASGNXPO2.765441
5SKYWEAC2.763877
6RJETEAC2.641551
7ABMXPO2.617283
8SKYWXPO2.610225
9FCXPO2.599589
10FIXXPO2.587622
11UFPIXPO2.444511
12PRSCXPO2.423591
13LABLXPO2.40627
14KAIXPO2.393814
15CRRCXPO2.390866

<tbody>
</tbody>


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

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
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
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
Give this a trial. Dave
Code: 
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
 
Upvote 0
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:

RJETXPO2.833939
SKYWEAC2.763877
KBALBKEYW1.982458
RJETNPO1.92518
SKYWSNHY1.914606
RJETGY1.854796
VSECXONE1.844602
SKYWDGI1.843945
SKYWSWFT1.771905
KBALBCUB1.76878
RJETSAAS1.764941
FSSATU1.734972
FSSWAGE1.733235
FSSLOCK1.722323
SKYWEME1.645214
SKYWSTNG1.644388
FSSULTI1.636401
RJETISSC1.633952
FSSAYI1.627569
ACCONAT1.612394
RJETECHO1.593331
SKYWGVA1.557466
SKYWPRLB1.546574
SKYWPRIM1.538642
SKYWORB1.518543
SKYWAIN1.518532
SKYWAIT1.513101

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>


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!



RJETXPO2.833939
FSSXPO2.805035
VSECXPO2.785968
ASGNXPO2.765441
SKYWEAC2.763877
RJETEAC2.641551
ABMXPO2.617283
SKYWXPO2.610225
FCXPO2.599589
FIXXPO2.587622
UFPIXPO2.444511
PRSCXPO2.423591
LABLXPO2.40627
KAIXPO2.393814
CRRCXPO2.390866
CMCOXPO2.389256
MWAXPO2.384641
GNRCXPO2.376736
MTZXPO2.359929
NCIXPO2.354649
POWLXPO2.351528
TBIXPO2.330272
SAIAXPO2.328209
USTRXPO2.324989
ASGNEAC2.30931
APOGXPO2.301142
BCOXPO2.299756
HAXPO2.29064
KBALBXPO2.270594
PGTIXPO2.265024
CEBXPO2.262908
UACLXPO2.258017
MSAXPO2.249153
EGLXPO2.242668
HNIXPO2.24118
SPBXPO2.235594
MTZEAC2.230917
ALGTXPO2.216751
HCKTXPO2.213907
VSECEAC2.211258
GKXPO2.206138
GFFXPO2.20174
SAIAEAC2.191742
ISHXPO2.189687
FSSEAC2.185216
CIRXPO2.183912
WSOXPO2.180773
SCSXPO2.179946
QUADXPO2.17326
DLXXPO2.172244
FIXEAC2.168338
ALGXPO2.164698
NWPXXPO2.164064
IIINXPO2.16331
ICFIXPO2.151547
CGXXPO2.146141
IIINEAC2.140881
VPRTXPO2.139588
FCEAC2.131737
EBFXPO2.123107
OTTREAC2.116559
UACLEAC2.116437
UNFXPO2.114694
PACRXPO2.112746
ARIIXPO2.111949
HNIEAC2.109716
PKOHXPO2.109688
DCOXPO2.109601
RXNXPO2.107999
WIREXPO2.107751
MNTXXPO2.106761
PRSCEAC2.094465
MWAEAC2.088072
ABMEAC2.07839
OTTRXPO2.076723
KFRCXPO2.076398
SWHCXPO2.073804
AIMCXPO2.061114
ALGTEAC2.060652
CWXPO2.059293
BLDRXPO2.059255
MOG.AXPO2.058032
SPBEAC2.047214
PIKEEAC2.045063
HDNGXPO2.044578
ECOLXPO2.042379
DXPEXPO2.040843
WNCXPO2.038954
XRMXPO2.031008
TPCXPO2.027007
LABLEAC2.025659
BBSIXPO2.023795
APOGEAC2.014695
KFYXPO2.013276
PATRXPO1.995858
AIMCEAC1.991982
MLIXPO1.989521
HURNXPO1.98886
TBIEAC1.983713
KBALBKEYW1.982458
CRRCEAC1.979702
JBLUXPO1.977941
PGTIEAC1.977305
FSTRXPO1.973703
BLDREAC1.972931
GNRCEAC1.969726
POWLEAC1.956923
PKOHEAC1.952256
UNFEAC1.950115
BCOEAC1.942053
MYRGXPO1.939376
RJETKEYW1.936522
GFFEAC1.927417
RJETNPO1.92518
USTREAC1.921987
LYTSXPO1.918697
CMCOEAC1.914878
SKYWSNHY1.914606
MSAEAC1.911118
CEBEAC1.904488
SKYWKEYW1.904135
MATXXPO1.903457
AIRXPO1.902878
CGXEAC1.883541
GSOLXPO1.876326
DLXEAC1.875558
ROCKEAC1.868998
SCSEAC1.860357
RJETGY1.854796
PIKEXPO1.848365
KAIEAC1.848202
VSECXONE1.844602
SKYWDGI1.843945
XRMEAC1.837255
GKEAC1.83607
ARIIEAC1.83095
BRSSXPO1.829337
SKYWNPO1.828435
FSTREAC1.821324
SKYWGY1.813646
VVIXPO1.810509
RUSHAXPO1.805398
HDNGEAC1.797803
UFPIEAC1.786421
NTKXPO1.780579
WNCEAC1.776963
SKYWSWFT1.771905
RUSHAEAC1.770475
KBALBCUB1.76878
JBLUEAC1.766136
RJETSAAS1.764941
BOOMXPO1.764669
ICFIEAC1.760892
WSOEAC1.748074
DCOEAC1.74147
QUADEAC1.739313
ASGNNPO1.739061
ALGEAC1.738797
NWPXEAC1.736562
FSSATU1.734972
ASGNGY1.734165
FSSWAGE1.733235
NCIEAC1.731301
PACREAC1.728112
ASGNKEYW1.726129
FSSLOCK1.722323
CIREAC1.722058
PIKEKEYW1.721236
ECOLEAC1.721007
RJETATU1.715298
ULTRXPO1.714285
RJETSWFT1.708069
RJETDGI1.703565
BBSIEAC1.702937
FSSNPO1.700255
MNTXEAC1.695826
FSSGY1.694567
SKYWLOCK1.690406
TPCEAC1.689735
ASGNATU1.683518
OTTRKEYW1.681653
VSECNPO1.68158
SKYWWAGE1.680104
MTZKEYW1.679199
FSSSWFT1.670881
VSECATU1.667034
ULTREAC1.665785
SKYWATU1.664102
VSECSWFT1.660736
SKYWSAAS1.660274
VSECCUB1.659365
NTKEAC1.658132
AEGNXPO1.655248
FCKEYW1.651474
VVIEAC1.648932
MYRGEAC1.647977
VSECWAGE1.646805
SKYWEME1.645214
SKYWSTNG1.644388
BLDRKEYW1.643799
TWINEAC1.642074
ASGNDGI1.640225
FSSDGI1.637768
FSSULTI1.636401
FCDGI1.634745
RJETISSC1.633952
ENOCEAC1.63071
RJETSTNG1.6302
FSSAYI1.627569
FSSSNHY1.627023
FCGY1.626582
VPRTEAC1.624705
FSSCUB1.623646
VSECGY1.621856
SKYWISSC1.618656
ASGNWAGE1.618422
FIXSNHY1.615855
ACCONAT1.612394
MOG.AEAC1.607425
APOGKEYW1.60305
FCLOCK1.602422
RJETSNHY1.600884
SKYWAYI1.600819
FIXSWFT1.598871
ASGNLOCK1.595357
MLIEAC1.594709
RJETECHO1.593331
LYTSEAC1.589621
FIXLOCK1.58838
FIXWAGE1.584517
NTKKEYW1.584298
EBFEAC1.578296
FCWAGE1.576843
ENOCXPO1.576682
FCATU1.575383
RJETEME1.57297
ASGNSWFT1.571777
FIXATU1.57144
FCNPO1.570936
KBALBDGI1.569923
ASGNULTI1.569147
HURNEAC1.56654
FIXNPO1.564877
KFYEAC1.563332
FSSSTNG1.562396
ASGNXONE1.56058
FIXGY1.559944
SKYWGVA1.557466
SPBKEYW1.553933
ROCKXPO1.546865

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>
 
Upvote 0
Here is a formula version that might work for you:

ABCDEFG
1RJETXPO2.8339391RJETXPO2.833939
2FSSXPO2.8050355SKYWEAC2.763877
3VSECXPO2.785968
4ASGNXPO2.765441
5SKYWEAC2.763877
6RJETEAC2.641551
7ABMXPO2.617283
8SKYWXPO2.610225
9FCXPO2.599589
10FIXXPO2.587622
11UFPIXPO2.444511
12PRSCXPO2.423591
13LABLXPO2.40627
14KAIXPO2.393814
15CRRCXPO2.390866
16

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2​

Worksheet Formulas
CellFormula
E1=A1
F1=B1
G1=C1
E2=IF($D2<>"",INDEX(A:A,$D2))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
D2{=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),""))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>



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.
 
Upvote 0
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
Code: 
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
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,215,059
Messages
6,122,918
Members
449,093
Latest member
dbomb1414

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top