Most common combinations in a worksheet

[DeasBg]

Hi
I found this earlier post about finding the most common combinations of 3 in a worksheet:

https://www.mrexcel.com/forum/excel-questions/898307-find-most-common-combination-3-a.html

I have a similar problem, but instead of 6 coloums and combinations of 3, I have 20 coloums and want to find combinations of 10.
I've tried to change both the macros given in the other post, but my knowledge of macros are very basic so it gets wrong every time I try.

Can anyone help me rewrite either of the macros so it will work for my situation?

 

[Eric W]

Did you try the macro from post # 9? Check post # 8 to see the 6 locations in the macro that you'll need to change: the input sheet, output sheet, and 2 parameters.

This macro reads the entire table in one shot to save time. Then it processes one line at a time. First it sorts the numbers in the line internally, then creates every possible combination of numbers, then adds 1 to each combination. It keeps track of the maximum count as it goes.

On further reflection though, it occurs to me that the output list of combinations might exceed 32K, which would cause a problem with the output. If that's the case, simply replace this line:

Sheets("Sheet9").Range("B1").Resize(Results.Count) = WorksheetFunction.Transpose(Results.keys)

with

    Application.ScreenUpdating = False
    MyTab = Results.keys
    For i = 0 To UBound(MyTab)
        Sheets("Sheet9").Cells(i + 1, "B") = MyTab(i)
    Next i
    Application.ScreenUpdating = True

 

[StephenCrump]

If potentially there was some pattern to the numbers, I'd be tempted to start by testing combinations of the most frequently occurring numbers, which combined with a bit of analysis could cut down the number of combinations to test by brute force.

At its simplest, let's say we test a manageable COMBIN(25,10) combinations for the 25 most frequently occurring numbers, and find that the most popular of these combinations occurs N times. That means we can exclude from further testing any other number that doesn't occur at least N times in the dataset.

But the question has the whiff of a lottery predictor (?) in which case we'd expect the numbers to be random, and we'd all be wasting our time .

 

[DeasBg]

Eric
Sorry for late response. I've tried your macro. It works, but like you said, it crashes when then number of rows gets to high. But I can easily change your macro if i want to find other combinations, like most common combination of 4 out of 10 columns or 3 out of 8 and so on. In these cases I haven't experienced a crash yet when the number of data is smaller. So I can use your macro to find whatever combinations I want then.

So thank you for helping me with this