Calculate number of combinations in a data set

[awiegmann1]

I have 200,000 rows of data (ID#'s), columns G-Q have an offer code from a set of 300 possible offers. Each of the twelve offers in cells G-Q are different for each ID#. The offer numbers are not in numerical order. What formula would I use to calculate the number of offer combinations in the 200,000 rows of data?

 

[jamtay317]

I'm not understanding what your asking, can you please post a small sample of data, and desired result?

 

[BenMiller]

Are you looking to count unique values in a range?

 

[shg]

If the 11 offers are selected at random from the 300 possibilities, there is a vanishingly small chance that there are any duplicates at all.

You could write a macro that sorts each row, then do an 11-way sort on cols G:Q, then look for duplicates in adjacent rows.

 

[awiegmann1]

If the 11 offers are selected at random from the 300 possibilities, there is a vanishingly small chance that there are any duplicates at all.

You could write a macro that sorts each row, then do an 11-way sort on cols G:Q, then look for duplicates in adjacent rows.

The offers are selected by relevancy to that ID# but it could be that none are duplicated. I've tried a macro to sort across the rows but it is taking too long to run. I was hoping for a formula that may be quicker.

My end goal is to know how many offer sets (combination of twelve for each ID#), if any, are duplicated in the data set.

Any help is appreciated. The answer would be really helpful to have by tomorrow.

 

[shg]

What macro did you try?

 

[awiegmann1]

What macro did you try?

Sub horizontalsort()
For x = 2 To 200009
myrange = "F" & x & ":" & "Q200009"
Worksheets("Sheet1").Range(myrange).sort _
Key1:=Worksheets("Sheet1").Rows(x), _
Orientation:=xlSortRows
Next x
End Sub

 

[shg]

You want to sort one row at a time:

Sub HorizontalSort()
    Dim rRow        As Range
    For Each rRow In Worksheets("Sheet1").Range("F2:Q9").Rows
        rRow.Sort Key1:=rRow.Cells(1), _
                  Orientation:=xlSortRows, _
                  Header:=xlNo
    Next rRow
End Sub

 

[shg]

Oops:

For Each rRow In Worksheets("Sheet1").Range("F2:[COLOR=#FF0000]Q200009[/COLOR]").Rows