Generate combinations of column with Fixed and column with Variable Values

S

SidKol

New Member
Joined
Oct 14, 2015
Messages
47
Hi all,

I'm playing around with VBA to find different ways of combination generation.

I have been able to create a tool to generate all possible combinations of FIVE values from one column
Order is not important
No repetition allowed

Input:
[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]Variable[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD]I[/TD]
[/TR]
</tbody>[/TABLE]


Output: (all combinations of five values, no repetition, order not important)
[TABLE="class: grid, width: 389"]
<tbody>[TR]
[TD]RESULTS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]G[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD]F[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]E[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]E[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
</tbody>[/TABLE]

This is the code:
Code: 
Sub combigenerator()


    Dim X As Long
    Dim a As Long, b As Long, c As Long, d As Long, e As Long
    Dim TotalValuesB As Integer


    'start from row 20 with results
    X = 20
    
    'count all variables in column B
    TotalValuesB = WorksheetFunction.CountIf(Range("B4:B15"), ("*?"))
    
    
    'clear previous result data
    With Sheets("Sheet1")
    .Rows(X & ":" & .Rows.Count).Delete
    End With
    


    'Loop combinations (no repetition, order not important)
    For a = 4 To (TotalValuesB + 3)
        For b = (a + 1) To (TotalValuesB + 3)
            For c = (b + 1) To (TotalValuesB + 3)
                For d = (c + 1) To (TotalValuesB + 3)
                    For e = (d + 1) To (TotalValuesB + 3)
       


          'Create a row with results
                        Cells(X, 1) = Cells(a, 2)
                        Cells(X, 2) = Cells(b, 2)
                        Cells(X, 3) = Cells(c, 2)
                        Cells(X, 4) = Cells(d, 2)
                        Cells(X, 5) = Cells(e, 2)
                        
        X = X + 1


        Next e
        Next d
        Next c
        Next b
        Next a
End Sub


Now I want to add a second column with FIXED Values for the input

Input:

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]FIXED[/TD]
[TD]Variable[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]C[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]D[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]I[/TD]
[/TR]
</tbody>[/TABLE]


Meaning that each combination should always start with ALL fixed values.
In this example: A and B
(But can also be for example E, F and I)

So desired output in this case is:

[TABLE="class: grid, width: 389"]
<tbody>[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]G[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD]F[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
</tbody>[/TABLE]



I have been playing around with nested looping, GoTo Labels, ElseIf etc, but I am receiving error after error whatever I try.
Can somebody point me in the direction of an efficient way to make this combination generator work for both fixed and variable values?

Any help greatly appreciated :)
 
Last edited:

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
Here's my solution:


Book1
ABCDE
3FixedVariableCount
4CE5
5DF
6G
7H
8I
20CDEFG
21CDEFH
22CDEFI
23CDEGH
24CDEGI
25CDEHI
26CDFGH
27CDFGI
28CDFHI
29CDGHI
Sheet1


Code: 
Public Sub GenerateCombinations()

Const StartRow = 20

Dim itemCount As Long
Dim fixedCount As Long
Dim variableCount As Long
Dim i As Long
Dim nextRow As Long
Dim ticker As Long

' Clear out previous results
If Cells(StartRow, "A").Value <> "" Then
    Range(Cells(StartRow, "A"), Cells(Cells(Rows.Count, "A").End(xlUp).Row, "A")).EntireRow.ClearContents
End If

' Get counts
itemCount = Range("C4").Value
fixedCount = Cells(StartRow, "A").End(xlUp).Row - 3
variableCount = Cells(StartRow, "B").End(xlUp).Row - 3

' Set up counters
ReDim pickers(itemCount - fixedCount) As Long
For i = 1 To itemCount - fixedCount
    pickers(i) = i
Next i

' Start of creating all combinations here
nextRow = StartRow
Do While True
    ' Fixed values
    For i = 1 To fixedCount
        Cells(nextRow, i).Value = Cells(i + 3, "A").Value
    Next i
    
    ' Variable values
    For i = 1 To itemCount - fixedCount
        Cells(nextRow, i + fixedCount).Value = Cells(pickers(i) + 3, "B").Value
    Next i
    
    ' Tick
    nextRow = nextRow + 1
    ticker = itemCount - fixedCount
    Do While True
        pickers(ticker) = pickers(ticker) + 1
        If pickers(ticker) > variableCount - (itemCount - fixedCount - ticker) Then
            ticker = ticker - 1
            If ticker = 0 Then Exit Do
        Else
            Exit Do
        End If
    Loop
    
    ' Finished?
    If ticker = 0 Then Exit Do
    
    ' Set up subsequent pickers
    If ticker < itemCount - fixedCount Then
        For i = ticker + 1 To itemCount - fixedCount
            pickers(i) = pickers(i - 1) + 1
        Next i
    End If
Loop

End Sub

WBD
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Search/Find specific word or match in multiple columns and highlight the cells that do not match
Replies
1
Views
37
chueeee
C
ExcelNewbie2020
  • Solved
extract the header of sets of non empty cell
Replies
3
Views
95
ExcelNewbie2020
ExcelNewbie2020
serge
  • Question Question
#N/A cell skip.
Replies
3
Views
268
Peter_SSs
Peter_SSs
M
  • Solved
Unique() filter to exclude blanks and multiple strings
Replies
2
Views
527
metcala
M
G
  • Question Question
Rearrange data into two columns
Replies
3
Views
66
Fluff
Fluff

Forum statistics

Threads
1,223,894
Messages
6,175,252
Members
452,623
Latest member
Techenthusiast

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top