Remove Duplicate Combinations

[JoJoBug85]

Hi All,

I am trying to compile a list of optimal fantasy football lineups, but I am running into a duplicate issue. I am able to have 3 wide-receivers playing in my lineup. I identified what I think are the top 75 wide receivers and plugged them in to Access to find all 405,150 possible combinations where WR1<>WR2 and WR3<>WR2 or WR1. The problem is that I then have each possible combination listed 6 times:

WR1	WR2	WR3
Antonio Brown	Larry Fitzgerald	Martavis Bryant
Antonio Brown	Martavis Bryant	Larry Fitzgerald
Larry Fitzgerald	Antonio Brown	Martavis Bryant
Larry Fitzgerald	Martavis Bryant	Antonio Brown
Martavis Bryant	Antonio Brown	Larry Fitzgerald
Martavis Bryant	Larry Fitzgerald	Antonio Brown

<tbody>
</tbody>

I have been wracking my brain and combing the internet, but must somehow not be searching for the right thing. I can't be the only person who has ever had this problem. I'm willing to do the work, but do you have any suggestions as to how I might isolate one of the combinations from each set of six and eliminate the five 'duplicates' to get down to the ~67,500 that I actually want? I'm fairly comfortable with excel/vba and access/modules, so I'm open to anything that gets me headed in the right direction.

Thanks in advance!!

 

[kalak]

See if this works for you.

Sub kalx()

Dim Qix As Object, a, i As Long, r As Long, rs As Long
Set Qix = CreateObject("scripting.dictionary")
Set a = Range("A1").CurrentRegion.Resize(, 3)

For i = 2 To a.Rows.Count
    a.Rows(i).Sort a(i, 1), Header:=xlNo, Orientation:=xlLeftToRight
    Qix(Join(Array(a(i, 1), a(i, 2), a(i, 3)), Chr(2))) = 1
Next i

a.Offset(1).ClearContents: r = 1
For Each c In Qix.keys
    r = r + 1
    a(r, 1).Resize(, 3) = Split(c, Chr(2))
Next c

End Sub

 

[MarcelBeug]

Another approach is to avoid duplicates.
I have the following formula solution to compile a list of 3 indices 1-75 without duplicates.
A2=1, B2=2, C2=3
A3: =IF(AND(B2=74,C2=75),A2+1,A2)
B3: =IF(C2=75,IF(B2=74,A2+1,B2+1),B2)
C3: =IF(C2=75,B3+1,C2+1)
Copy formulas from row 3 down thru row 70,226 for all combinations.
With INDEX you can create columns with names for the indices.

 

[StephenCrump]

B3: =IF(C2=75,IF(B2=74,A3+1,B2+1),B2)
Copy formulas from row 3 down thru row 70,226 for all combinations.

... or correct this typo, and copy down only to row 67,526

 

[JoJoBug85]

You are all wonderful! Both solutions (the second one with the fix added in to remove duplicates in columns A and B) worked wonderfully. I turned off screen updating on the vba and it ran like a charm. Now on to the rest of my lineup Thank you so much for your quick and helpful suggestions!

 

[MarcelBeug]

... or correct this typo, and copy down only to row 67,526

Oops ... thanks Stephen!