find all combinations (no repeats)

**Paul_Hossler** · 03-14-2021, 08:03 AM

I sort of understand, but to p45cal's comment ...

What on earth are you going to do with 6.25 million rows of data?!

it would seem to me that just listing the acceptable combinations would be better

**p45cal** · 03-14-2021, 08:21 AM

Originally Posted by Paul_Hossler

it would seem to me that just listing the acceptable combinations would be better

If I've understood what Paul means by acceptable combinations, my 6.25 million rows are the acceptable ones, the macro has excluded some 13 million already!
What on earth can a human do with more than 6 million rows of data - I'd have difficulty handling 10 rows!

**Paul_Hossler** · 03-14-2021, 09:37 AM

I was just scratching my head over the simplified Book2 example in post #35

Capture.JPG

There were 12 'results', but only one was 'Acceptable' (approx 8%). It appeared to be more a traverse problem to find an acceptable path than a combinations problem. Guess I was wrong

In the full example and using p45cal's macro the 6 columns of numbers look like there would be 14 x 19 x 19 x19 x20 x 11 = 21,125,720 to be checked, with 6,247,034 Acceptable (approx 30%)

Edit:

I'm still wondering what 6M+ results could be used for

**NIMANIMA50** · 03-16-2021, 10:09 PM

Hi p45cal,

Could you please have a look at the attached excel file as an exampleBook1.xlsx. I have left a note about the criteria.

Thanks

**p45cal** · 03-17-2021, 09:40 AM

There are about 40 million 'acceptable' combinations.
Output is to the Immediate pane:

Sub blah()
For a = 1 To 53
  For b = 2 To 54
    If b > a Then
      For c = 3 To 55
        If c > b Then
          For d = 4 To 56
            If d > c Then
              For e = 5 To 57
                If e > d Then
                  For f = 6 To 58
                    If f > e Then
                      Debug.Print a, b, c, d, e, f
                    End If
                  Next f
                End If
              Next e
            End If
          Next d
        End If
      Next c
    End If
  Next b
Next a
End Sub

**NIMANIMA50** · 03-17-2021, 04:10 PM

Originally Posted by p45cal

There are about 40 million 'acceptable' combinations.
Output is to the Immediate pane:

Sub blah()
For a = 1 To 53
  For b = 2 To 54
    If b > a Then
      For c = 3 To 55
        If c > b Then
          For d = 4 To 56
            If d > c Then
              For e = 5 To 57
                If e > d Then
                  For f = 6 To 58
                    If f > e Then
                      Debug.Print a, b, c, d, e, f
                    End If
                  Next f
                End If
              Next e
            End If
          Next d
        End If
      Next c
    End If
  Next b
Next a
End Sub

P45cal,

Thank you for your response. However there are some issues with your code.
1. it considers the numbers in DATA columns as fixed (1-58) but this columns has variable and mixed numbers (My fault I did not clearly explained at first).
2. I tried to run the code but Excel got frozen so I have to close it, but by looking at the code, it tries to list all combination in one sheet and separated by ",". I need the combinations to be placed in different cells (for instant A-F) and if can not fit in one sheet it will be split in different sheets (workbooks preferably) , like your previous code.
I have attached another file , I hope this file will be clear.

Thanks again

**Paul_Hossler** · 03-17-2021, 04:52 PM

I think it's easier to generate combinations down to row 1M and then move over 7 columns for the next 1M instead of messing with adding worksheets / workbooks (just my 2 cents)

Capture.JPG

This just does data in the same order as it's listed in Col A and will get messed up if a number is repeated in Data

Option Explicit


Sub Generate()
    Dim r As Range
    Dim v As Variant
    Dim n1 As Long, n2 As Long, n3 As Long, n4 As Long, n5 As Long, n6 As Long
    Dim rowCount As Long, colCount As Long
    Dim N As Long
    
    With Worksheets("Sheet2")
        Set r = .Range("A1")
        Set r = Range(r, r.End(xlDown))
        
        v = Application.WorksheetFunction.Transpose(r)
        N = UBound(v)
        
        rowCount = 1
        colCount = 3
        
        For n1 = 1 To N - 5
            For n2 = n1 + 1 To N - 4
                For n3 = n2 + 1 To N - 3
                    For n4 = n3 + 1 To N - 2
                        For n5 = n4 + 1 To N - 1
                            For n6 = n5 + 1 To N
                                .Cells(rowCount, colCount).Value = v(n1)
                                .Cells(rowCount, colCount + 1).Value = v(n2)
                                .Cells(rowCount, colCount + 2).Value = v(n3)
                                .Cells(rowCount, colCount + 3).Value = v(n4)
                                .Cells(rowCount, colCount + 4).Value = v(n5)
                                .Cells(rowCount, colCount + 5).Value = v(n6)
                                
                                rowCount = rowCount + 1
                                
                                If rowCount Mod 1000 = 0 Then
                                    Application.StatusBar = v(n1) & "," & v(n2) & "," & v(n3) & "," & v(n4) & "," & v(n5) & "," & v(n6)
                                    DoEvents
                                End If
                                
                                If rowCount = 1000001 Then
                                    rowCount = 1
                                    colCount = colCount + 7
                                End If
                            Next n6
                        Next n5
                    Next n4
                Next n3
            Next n2
        Next n1
    End With
    
    Application.StatusBar = False
    
    MsgBox "Done"
    
End Sub

**NIMANIMA50** · 03-17-2021, 05:19 PM

Originally Posted by Paul_Hossler

I think it's easier to generate combinations down to row 1M and then move over 7 columns for the next 1M instead of messing with adding worksheets / workbooks (just my 2 cents)

Capture.JPG

This just does data in the same order as it's listed in Col A and will get messed up if a number is repeated in Data

Option Explicit


Sub Generate()
    Dim r As Range
    Dim v As Variant
    Dim n1 As Long, n2 As Long, n3 As Long, n4 As Long, n5 As Long, n6 As Long
    Dim rowCount As Long, colCount As Long
    Dim N As Long
    
    With Worksheets("Sheet2")
        Set r = .Range("A1")
        Set r = Range(r, r.End(xlDown))
        
        v = Application.WorksheetFunction.Transpose(r)
        N = UBound(v)
        
        rowCount = 1
        colCount = 3
        
        For n1 = 1 To N - 5
            For n2 = n1 + 1 To N - 4
                For n3 = n2 + 1 To N - 3
                    For n4 = n3 + 1 To N - 2
                        For n5 = n4 + 1 To N - 1
                            For n6 = n5 + 1 To N
                                .Cells(rowCount, colCount).Value = v(n1)
                                .Cells(rowCount, colCount + 1).Value = v(n2)
                                .Cells(rowCount, colCount + 2).Value = v(n3)
                                .Cells(rowCount, colCount + 3).Value = v(n4)
                                .Cells(rowCount, colCount + 4).Value = v(n5)
                                .Cells(rowCount, colCount + 5).Value = v(n6)
                                
                                rowCount = rowCount + 1
                                
                                If rowCount Mod 1000 = 0 Then
                                    Application.StatusBar = v(n1) & "," & v(n2) & "," & v(n3) & "," & v(n4) & "," & v(n5) & "," & v(n6)
                                    DoEvents
                                End If
                                
                                If rowCount = 1000001 Then
                                    rowCount = 1
                                    colCount = colCount + 7
                                End If
                            Next n6
                        Next n5
                    Next n4
                Next n3
            Next n2
        Next n1
    End With
    
    Application.StatusBar = False
    
    MsgBox "Done"
    
End Sub

Thanks Paul,
I , however , prefer splitting the combination into different workbooks rather than sheets (or within one sheet). Having different workbooks will make working with the data much easier compared to when they all in one big heavy file.

**Paul_Hossler** · 03-17-2021, 05:28 PM

Write a second macro to take each million row chunk and put into a separate workbook, although I still fail to see how 40 x 10^6 six-tuples would ever be easy to work with.

Did you ever say just what the ultimate purpose was, or did I miss it?

**p45cal** · 03-17-2021, 06:21 PM

This is ludicrous.

1. Since the values don't have to increase any more from left to right, are (for example) both
1,2,3,4,5,6
and
1,2,3,4,6,5
required in the list?
(just the last 2 numbers are switched)

2. Will the starting column of numbers contain duplicates?

**NIMANIMA50** · 03-17-2021, 06:52 PM

Originally Posted by p45cal

This is ludicrous.

1. Since the values don't have to increase any more from left to right, are (for example) both
1,2,3,4,5,6
and
1,2,3,4,6,5
required in the list?
(just the last 2 numbers are switched)

2. Will the starting column of numbers contain duplicates?

p45cal,

The order of combination is not required. as you said, 1,2,3,4,5,6 and 1,2,3,4,6,5 are the same and only one needs to be listed.
this is just combination without repetition that we use this formula to calculate the total number of combination.comb.jpg
in this case k=6 and n is variable depends on the count of numbers in column "DATA".

**p45cal** · 03-18-2021, 01:47 AM

Again,

Originally Posted by p45cal

2. Will the starting column of numbers contain duplicates?

**NIMANIMA50** · 03-18-2021, 01:59 AM

Originally Posted by p45cal

Again,

I dont understand your question. can you give an example?

**p45cal** · 03-18-2021, 02:32 AM

Originally Posted by NIMANIMA50

I dont understand your question. can you give an example?

2021-03-18_093147.png

**NIMANIMA50** · 03-18-2021, 02:55 AM

Originally Posted by p45cal

2021-03-18_093147.png

It doesn't matter as long as no duplicate in the combination

**p45cal** · 03-18-2021, 03:19 AM

Originally Posted by NIMANIMA50

It doesn't matter as long as no duplicate in the combination

I'll take that as a yes. It does matter because it needs to be catered for in the code you're asking for; it just means that the process of finding the combinations could be a lot longer, or that we have to do some deduplicating before searching for combinations.

**Paul_Hossler** · 03-18-2021, 08:25 AM

Originally Posted by p45cal

I'll take that as a yes. It does matter because it needs to be catered for in the code you're asking for; it just means that the process of finding the combinations could be a lot longer, or that we have to do some deduplicating before searching for combinations.

Sorting low-to-high probably wouldn't hurt either

**p45cal** · 03-18-2021, 12:44 PM

Try the attached.

**NIMANIMA50** · 03-20-2021, 01:15 AM

Originally Posted by p45cal

Try the attached.

I tried the list in the attached file but nothing generated.

**p45cal** · 03-20-2021, 03:45 AM

Your last attachment works fine here. You'll have to wait a bit.
If you want to reassure yourself that it's working, change the line:
ResultBlockSize = 1000000
to say:
ResultBlockSize = 100000
which should produce some 24 new workbooks with your data.

I'm presuming the file I attached to msg#58 worked OK.

Thread: find all combinations (no repeats)

Thread Tools

Display

Posting Permissions