find all combinations (no repeats)

[p45cal]

Try the likes of

Sub Blah()
Set Source = Range("A1:A15")
Set Destn = Range("B1")
For g = 1 To 12
  For h = g + 1 To 13
    For i = h + 1 To 14
      For j = i + 1 To 15
        Destn.Value = Join(Array(Source.Cells(g), Source.Cells(h), Source.Cells(i), Source.Cells(j)), ",")
        Set Destn = Destn.Offset(1)
      Next
    Next
  Next
Next
End Sub

[p45cal]

or faster:

Sub Blah2()
Set Source = Range("A1:A15")
SceVals = Source.Value
Set Destn = Range("C1")
myCount = Application.WorksheetFunction.Combin(UBound(SceVals), 4)
ReDim Results(1 To myCount, 1 To 1)
k = 1
For g = 1 To UBound(SceVals) - 3
  For h = g + 1 To UBound(SceVals) - 2
    For i = h + 1 To UBound(SceVals) - 1
      For j = i + 1 To UBound(SceVals)
        Results(k, 1) = Join(Array(SceVals(g, 1), SceVals(h, 1), SceVals(i, 1), SceVals(j, 1)), ",")
        k = k + 1
      Next
    Next
  Next
Next
Destn.Resize(myCount).Value = Results
End Sub

[snb]

Especially designed for you:

http://www.snb-vba.eu/VBA_Combinations_en.html

[Paul_Hossler]

Try this

Option Explicit


Sub C15_4()
    Const cA As Long = 65       '   use number values
    Const cO As Long = 79       '   ASCII A = 65, ASCII O = 79
    
    Dim i1 As Long, i2 As Long, i3 As Long, i4 As Long, o As Long
    
    o = 1               '   starting output row
    
    For i1 = cA To cO   '   A - L
        For i2 = i1 + 1 To cO
            For i3 = i2 + 1 To cO
                For i4 = i3 + 1 To cO
                    ActiveSheet.Cells(o, 1).Value = Chr(i1) & Chr(i2) & Chr(i3) & Chr(i4)
                    o = o + 1
                Next i4
            Next i3
        Next i2
    Next i1


    MsgBox "Done"


End Sub

[Msimo]

THANK Y'ALL SO SO SO MUCH!!!! PROBLEM SOLVED PERFECTLY. I APPRECIATE THE HELP

[worldnew29]

same issue. thanks for all you guys. it's quite helpful for me so solve the problem.

[NIMANIMA50]

Hi Paul,

I was trying to figure out an Excel VBA for combination of 6 columns of numbers. Lets say columns A-F contain numbers. I want to generate all the combination of these 6 columns without repetition and considering the criteria below for generated combination( assuming the combinations are stored in COLUMNS G-L):

G<H<I<J<K<L

I appreciated if you could help on this.

[p45cal]

There is only one combination with those criteria.
All you need to do is sort horizontally.

[NIMANIMA50]

There is only one combination with those criteria.
All you need to do is sort horizontally.

Hi p45cal,

I am not sure if I am following you. each columns has about 35-45 numbers. How can you get all the combinations as it is required with the method you mentioned?

[p45cal]

OK. Maybe I've misunderstood; best attach a workbook.

[NIMANIMA50]

OK. Maybe I've misunderstood; best attach a workbook.

p45cal,

Please see attach workbook as an example.

Thanks

[p45cal]

You realise there are about 6.25 million combinations that meet your criteria?
(21 million+ ignoring criteria)

How do you want the results, bearing in mind there are only about 1 million rows on a sheet?

[Paul_Hossler]

How good is your example?

1. I can see that the Green is acceptable -- G<H<I<J<K<L

2. I can see that the Orange is not acceptable -- G>H

3. Where did the Blue come from?

Capture.JPG

Lets say columns A-F contain numbers. I want to generate all the combination of these 6 columns without repetition and considering the criteria below for generated combination (assuming the combinations are stored in COLUMNS G-L): G<H<I<J<K<L

I don't see where 'All Combinations' comes in

For example

1

10

24

39

45

55

can be arranged 720 ways (6!) and there's only 1 way G<H<I<J<K<L


Likewise

9

6

20

40

41

51

can be arranged 720 ways and 6-9-20-40-41-51 would be the answer

It sort of / almost / kind of looks like you just want to take 6 numbers and see if G<H<I<J<K<L, returning a Yes/No answer

[NIMANIMA50]

You realise there are about 6.25 million combinations that meet your criteria?
(21 million+ ignoring criteria)

How do you want the results, bearing in mind there are only about 1 million rows on a sheet?

Thanks p45cal,

What I need is a VBA code to generate all those combination.

Thanks

[NIMANIMA50]

How good is your example?

1. I can see that the Green is acceptable -- G<H<I<J<K<L

2. I can see that the Orange is not acceptable -- G>H

3. Where did the Blue come from?

Capture.JPG




I don't see where 'All Combinations' comes in

For example

1

10

24

39

45

55

can be arranged 720 ways (6!) and there's only 1 way G<H<I<J<K<L


Likewise

9

6

20

40

41

51

can be arranged 720 ways and 6-9-20-40-41-51 would be the answer

It sort of / almost / kind of looks like you just want to take 6 numbers and see if G<H<I<J<K<L, returning a Yes/No answer

Paul,

I attached another file that might make the concept clearer for you. For the sake of simplicity I consider only 4 columns (note: this is an example , I need a VBA code to work for 6 columns).

Thanks

thanks

[p45cal]

What I need is a VBA code to generate all those combination.

I've done that, so again:

You realise there are about 6.25 million combinations that meet your criteria?
How do you want the results, bearing in mind there are only about 1 million rows on a sheet?

[NIMANIMA50]

I've done that, so sgain:

p45cal ,

can we split the result in 7 or or worksheets/ workbooks?

thanks

[p45cal]

In the attached is a button in the vicinity of cell J8.
Here, it takes the code about 5 seconds to get the 6.25 million results, about 40 secs to write them to the sheets.

Note that there is nothing in the code to eliminate repeats; at the moment there are none because none of the individual source columns contain any duplicates. If duplicates do occur in the original columns the code will need a tweak.

[NIMANIMA50]

In the attached is a button in the vicinity of cell J8.
Here, it takes the code about 5 seconds to get the 6.25 million results, about 40 secs to write them to the sheets.

THANKS A LOT P45CAL. Seems working perfectly.

[p45cal]

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