Generate combinations of column with Fixed and column with Variable Values

[SidKol]

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:

	A	B
1
2
3		Variable
4		C
5		D
6		E
7		F
8		G
9		H
10		I

<tbody>
</tbody>

Output: (all combinations of five values, no repetition, order not important)

RESULTS
C	D	E	F	G
C	D	E	F	H
C	D	E	F	I
C	D	E	G	H
C	D	E	G	I
C	D	E	H	I
C	D	F	G	H
C	D	F	G	I
C	D	F	H	I
C	D	G	H	I
C	E	F	G	H
C	E	F	G	I
C	E	F	H	I
C	E	G	H	I
C	F	G	H	I
D	E	F	G	H
D	E	F	G	I
D	E	F	H	I
D	E	G	H	I
D	F	G	H	I
E	F	G	H	I

<tbody>
</tbody>

This is the 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:

	A	B
1
2
3	FIXED	Variable
4	C	E
5	D	F
6		G
7		H
8		I

<tbody>
</tbody>

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:

C	D	E	F	G
C	D	E	F	H
C	D	E	F	I
C	D	E	G	H
C	D	E	G	I
C	D	E	H	I
C	D	F	G	H
C	D	F	G	I
C	D	F	H	I
C	D	G	H	I

<tbody>
</tbody>

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

 

[wideboydixon]

Here's my solution:

Book1
	A	B	C	D	E
3	Fixed	Variable	Count
4	C	E	5
5	D	F
6		G
7		H
8		I
20	C	D	E	F	G
21	C	D	E	F	H
22	C	D	E	F	I
23	C	D	E	G	H
24	C	D	E	G	I
25	C	D	E	H	I
26	C	D	F	G	H
27	C	D	F	G	I
28	C	D	F	H	I
29	C	D	G	H	I
Sheet1

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

 

[SidKol]

Wow, you're a machine!
Thanks a lot WBD! So much appreciated!