VBA Code - Generate a list of all combinations possibles

alexlimmy · Jul 20th 2017

Hi.

Using a VBA Code, I'm looking generate a results list of all combinations (in individuals cells) from a list of variables across 4 different columns . Please find below an example;

Initial list of variables:
[TABLE="width: 500"]

[tr]

[td]

Column A

[/td]

[td]

Column B

[/td]

[td]

Column C

[/td]

[td]

Column D

[/td]

[/tr]

[tr]

[td]

1

[/td]

[td]

4

[/td]

[td]

7

[/td]

[td]

9

[/td]

[/tr]

[tr]

[td]

2

[/td]

[td]

5

[/td]

[td]

8

[/td]

[td]

10

[/td]

[/tr]

[tr]

[td]

3

[/td]

[td]

6

[/td]

[td][/td]

[/tr]

[/TABLE]

Final results:

[TABLE="width: 500"]

[tr]

[td]

Result 1

[/td]

[td]

Result 2

[/td]

[td]

Result 3

[/td]

[td]

Result 4

[/td]

[/tr]

[tr]

[td]

1

[/td]

[td]

4

[/td]

[td]

7

[/td]

[td]

9

[/td]

[/tr]

[tr]

[td]

2

[/td]

[td]

4

[/td]

[td]

7

[/td]

[td]

9

[/td]

[/tr]

[tr]

[td]

3

[/td]

[td]

4

[/td]

[td]

7

[/td]

[td]

9

[/td]

[/tr]

[tr]

[td]

1

[/td]

[td]

5

[/td]

[td]

7

[/td]

[td]

9

[/td]

[/tr]

[/TABLE]

Thanks for any help in advance.

**mikerickson** · Jul 20th 2017

Re: VBA Code - Generate a list of all combinations possibles

This uses a UDF. Given a one combination (like result 2,4,7,9) it will return the next combination (3,4,7,9).

The ThisCombination argument of NextCombination is range of the current combination.
The Digits argument is the initial range of variables.

In the attached, H1:K1 is blank. H2:K2 holds the array formula {=NextCombination(H1:K1, $A$1:$D$3)}
(do not enter the { }, that is only there to indicate an array formula. they are not typed in.)
This returns the first combination 1,4,7,9
Drag the formula down to row 3 and the formula becomes {=NextCombination(H1:K1, $A$1:$D$3)} and the result 2,4,7,9
Drag further down and more combinations are returned.

Using a NextCombination approach, rather than listing all of them, has the advantage that one can start in the middle of the sequence and not go any further than one needs.

As the number of digits increases and the number of possible "letters" per digit increase, "all combinations" can quickly become a large number. This allows the user to work with high numbers of input factors, but not have to generate the entire list of combinations. Although every combination is accessible through this route.

Code

Function NextCombination(ThisCombination As Range, Digits As Range, Optional LeftToRight As Boolean = True) As Variant
    Dim DoMe As Long, Incriment As Long
    Dim ColCount As Long
    Dim Result As Variant, LetterIndex As Variant
    
    With Digits
        Set Digits = Application.Intersect(.EntireColumn, .CurrentRegion.EntireRow)
    End With
    ColCount = Digits.Columns.Count
    
    Result = ThisCombination.Resize(1, ColCount).Value
    
    If LeftToRight Then
        DoMe = 1
        Incriment = 1
    Else
        DoMe = ColCount
        Incriment = -1
    End If
    
    Do
        With Digits.Columns(DoMe)
            LetterIndex = Application.Match(Result(1, DoMe), .Cells, 0)
            
            If IsNumeric(LetterIndex) Then
                If .Cells(LetterIndex + 1, 1) = vbNullString Then
                    Result(1, DoMe) = .Cells(1, 1).Value
                Else
                    Result(1, DoMe) = .Cells(LetterIndex + 1, 1)
                    Exit Do
                End If
            Else
                Result(1, DoMe) = .Cells(1, 1).Value
            End If
        End With
        
        DoMe = DoMe + Incriment
    Loop Until (DoMe < 1) Or (ColCount < DoMe)
    
    NextCombination = Result
End Function

Display More

Note that in the attached file, Sheet2 has a 5 digit data set (and a 5 digit result) and sets the optional LeftToRight argument to False.

alexlimmy · Jul 20th 2017

Re: VBA Code - Generate a list of all combinations possibles

Thanks a bunch. This works a treat. Cheers.

alexlimmy · Jul 22nd 2017

Re: VBA Code - Generate a list of all combinations possibles

Hi mikerickson

If I wanted to achieve the exact same thing but to have it as a sub (code with a button) instead of a function. How might this look?

Cheers

**mikerickson** · Jul 23rd 2017

Re: VBA Code - Generate a list of all combinations possibles

I would hesitate doing this with a button, simply because one button push could easily result in millions of results.
But if you need to you could calcualte the number of rows needed and have you macro put the UDF into the appropriate rows and then copy/Paste Values.

rajeshtrk1986 · Apr 1st 2018

Hi, I am looking for a VBA codes to generate group of combinations from 11 of 22 elements which satisfy multiple condition. Thanks for your help in advance.
[TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 295"]

[tr]

[TD="class: xl63, width: 145"]Group[/TD]
[TD="class: xl63, width: 94"]Group-Task[/TD]
[TD="class: xl64, width: 62"]Name[/TD]
[TD="class: xl63, width: 38"]Value[/TD]
[TD="class: xl63, width: 52"]Capacity[/TD]

[/tr]

[tr]

[TD="class: xl66"]Group1[/TD]
[TD="class: xl66"]Group1-Base[/TD]
[TD="class: xl67"]A[/TD]
[TD="class: xl66"]9[/TD]
[TD="class: xl66"]50.73[/TD]

[/tr]

[tr]

[TD="class: xl66"]Group1[/TD]
[TD="class: xl66"]Group1-Audio[/TD]
[TD="class: xl67"]B[/TD]
[TD="class: xl66"]9[/TD]
[TD="class: xl66"]75.14[/TD]

[/tr]

[tr]

[TD="class: xl66"]Group2[/TD]
[TD="class: xl66"]Group2-Base[/TD]
[TD="class: xl67"]C[/TD]
[TD="class: xl66"]10[/TD]
[TD="class: xl66"]73.78[/TD]

[/tr]

[tr]

[TD="class: xl66"]Group2[/TD]
[TD="class: xl66"]Group2-Base[/TD]
[TD="class: xl67"]D[/TD]
[TD="class: xl66"]9[/TD]
[TD="class: xl66"]55.4[/TD]

[/tr]

[tr]

[TD="class: xl66"]Group2[/TD]
[TD="class: xl66"]Group2-Volume[/TD]
[TD="class: xl67"]E[/TD]
[TD="class: xl66"]8.5[/TD]
[TD="class: xl66"]35.61[/TD]

[/tr]

[tr]

[TD="class: xl66"]Group2[/TD]
[TD="class: xl66"]Group2-Audio[/TD]
[TD="class: xl67"]F[/TD]
[TD="class: xl66"]8.5[/TD]
[TD="class: xl66"]34.7[/TD]

[/tr]

[tr]

[TD="class: xl66"]Group1[/TD]
[TD="class: xl66"]Group1-Volume[/TD]
[TD="class: xl67"]G[/TD]
[TD="class: xl66"]8.5[/TD]
[TD="class: xl66"]18.3[/TD]

[/tr]

[tr]

[TD="class: xl66"]Group2[/TD]
[TD="class: xl66"]Group2-Volume[/TD]
[TD="class: xl67"]H[/TD]
[TD="class: xl66"]8.5[/TD]
[TD="class: xl66"]26.37[/TD]

[/tr]

[tr]

[TD="class: xl66"]Group1[/TD]
[TD="class: xl66"]Group1-Audio[/TD]
[TD="class: xl67"]I[/TD]
[TD="class: xl66"]9[/TD]
[TD="class: xl66"]59.62[/TD]

[/tr]

[tr]

[TD="class: xl66"]Group2[/TD]
[TD="class: xl66"]Group2-Volume[/TD]
[TD="class: xl67"]J[/TD]
[TD="class: xl66"]8.5[/TD]
[TD="class: xl66"]23.99[/TD]

[/tr]

[tr]

[TD="class: xl66"]Group1[/TD]
[TD="class: xl66"]Group1-Base[/TD]
[TD="class: xl67"]K[/TD]
[TD="class: xl66"]8[/TD]
[TD="class: xl66"]5.88[/TD]

[/tr]

[tr]

[TD="class: xl66"]Group2[/TD]
[TD="class: xl66"]Group2-Base[/TD]
[TD="class: xl67"]L[/TD]
[TD="class: xl66"]8[/TD]
[TD="class: xl66"]8.84[/TD]

[/tr]

[tr]

[TD="class: xl66"]Group2[/TD]
[TD="class: xl66"]Group2-Base[/TD]
[TD="class: xl67"]M[/TD]
[TD="class: xl66"]8[/TD]
[TD="class: xl66"]8.33[/TD]

[/tr]

[tr]

[TD="class: xl66"]Group1[/TD]
[TD="class: xl66"]Group1-Base[/TD]
[TD="class: xl67"]N[/TD]
[TD="class: xl66"]8.5[/TD]
[TD="class: xl66"]44[/TD]

[/tr]

[tr]

[TD="class: xl66"]Group2[/TD]
[TD="class: xl66"]Group2-Audio[/TD]
[TD="class: xl67"]O[/TD]
[TD="class: xl66"]9[/TD]
[TD="class: xl66"]71.99[/TD]

[/tr]

[tr]

[TD="class: xl66"]Group1[/TD]
[TD="class: xl66"]Group1-Volume[/TD]
[TD="class: xl67"]P[/TD]
[TD="class: xl66"]10[/TD]
[TD="class: xl66"]70.74[/TD]

[/tr]

[tr]

[TD="class: xl66"]Group1[/TD]
[TD="class: xl66"]Group1-Volume[/TD]
[TD="class: xl67"]Q[/TD]
[TD="class: xl66"]10.5[/TD]
[TD="class: xl66"]47.66[/TD]

[/tr]

[tr]

[TD="class: xl66"]Group1[/TD]
[TD="class: xl66"]Group1-Volume[/TD]
[TD="class: xl67"]R[/TD]
[TD="class: xl66"]8[/TD]
[TD="class: xl66"]10.06[/TD]

[/tr]

[tr]

[TD="class: xl66"]Group2[/TD]
[TD="class: xl66"]Group2-Audio[/TD]
[TD="class: xl67"]S[/TD]
[TD="class: xl66"]8.5[/TD]
[TD="class: xl66"]64.96[/TD]

[/tr]

[tr]

[TD="class: xl66"]Group1[/TD]
[TD="class: xl66"]Group1-Volume[/TD]
[TD="class: xl67"]T[/TD]
[TD="class: xl66"]9[/TD]
[TD="class: xl66"]63.29[/TD]

[/tr]

[tr]

[TD="class: xl66"]Group1[/TD]
[TD="class: xl66"]Group1-Singer[/TD]
[TD="class: xl67"]U[/TD]
[TD="class: xl66"]8.5[/TD]
[TD="class: xl66"]51.2[/TD]

[/tr]

[tr]

[TD="class: xl66"]Group2[/TD]
[TD="class: xl66"]Group2-Singer[/TD]
[TD="class: xl67"]V[/TD]
[TD="class: xl66"]9[/TD]
[TD="class: xl66"]58.35[/TD]

[/tr]

[/TABLE]

My criteria:
[TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 227"]

[tr]

[TD="class: xl67, width: 145"]Criteria[/TD]
[TD="class: xl67, width: 94"]Minimum[/TD]
[TD="class: xl68, width: 62"]Maximum[/TD]

[/tr]

[tr]

[TD="class: xl66"]Group1[/TD]
[TD="class: xl66"]4[/TD]
[TD="class: xl66"]7[/TD]

[/tr]

[tr]

[TD="class: xl66"]Group2[/TD]
[TD="class: xl66"]7[/TD]
[TD="class: xl66"]4[/TD]

[/tr]

[tr]

[TD="class: xl66"]Singer[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]1[/TD]

[/tr]

[tr]

[TD="class: xl66"]Base[/TD]
[TD="class: xl66"]3[/TD]
[TD="class: xl66"]5[/TD]

[/tr]

[tr]

[TD="class: xl66"]Audio[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]3[/TD]

[/tr]

[tr]

[TD="class: xl66"]Volume[/TD]
[TD="class: xl66"]3[/TD]
[TD="class: xl66"]5[/TD]

[/tr]

[tr]

[TD="class: xl66"]Combination Total Value[/TD]
[TD="class: xl66"]100[/TD]
[TD="class: xl66"] [/TD]

[/tr]

[/TABLE]

Also, need to publish the results with all the give data.

Networklive9 · Mar 16th 2019

Good

VBA Code - Generate a list of all combinations possibles

Participate now!

Share