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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]WR1
[/TD]
[TD]WR2
[/TD]
[TD]WR3
[/TD]
[/TR]
[TR]
[TD]Antonio Brown
[/TD]
[TD]Larry Fitzgerald
[/TD]
[TD]Martavis Bryant
[/TD]
[/TR]
[TR]
[TD]Antonio Brown
[/TD]
[TD]Martavis Bryant
[/TD]
[TD]Larry Fitzgerald
[/TD]
[/TR]
[TR]
[TD]Larry Fitzgerald
[/TD]
[TD]Antonio Brown
[/TD]
[TD]Martavis Bryant
[/TD]
[/TR]
[TR]
[TD]Larry Fitzgerald
[/TD]
[TD]Martavis Bryant
[/TD]
[TD]Antonio Brown
[/TD]
[/TR]
[TR]
[TD]Martavis Bryant
[/TD]
[TD]Antonio Brown
[/TD]
[TD]Larry Fitzgerald
[/TD]
[/TR]
[TR]
[TD]Martavis Bryant
[/TD]
[TD]Larry Fitzgerald
[/TD]
[TD]Antonio Brown
[/TD]
[/TR]
</tbody>[/TABLE]

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!