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:
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![Smile :) :)](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f642.png)
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
![Smile :) :)](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f642.png)
Last edited: