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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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

ExcelNewbie2020
  • Solved
extract the header of sets of non empty cell
Replies
3
Views
73
ExcelNewbie2020
ExcelNewbie2020
serge
  • Question Question
#N/A cell skip.
Replies
3
Views
247
Peter_SSs
Peter_SSs
M
  • Solved
Unique() filter to exclude blanks and multiple strings
Replies
2
Views
390
metcala
M
S
  • Question Question
Layered return values in Excel sheet
Replies
4
Views
89
lrobbo314
lrobbo314
A
  • Solved
Return Records if a match is found
Replies
4
Views
162
agentkramr
A

Forum statistics

Threads
1,223,231
Messages
6,170,885
Members
452,364
Latest member
springate

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