Excel vba to create every possible combination of a Range

Question

I have a problem that I haven't been able to find anywhere on the web (it may be there, but I can't find it, heh).

I have a spreadsheet with 13 columns of data. Each of the column contains variations of a parameter that needs to go into an overall test case.

All of them differ, like

E:
101%
105%
110%
120%

J:
Upper S
Upside L
Downside B
Premium V

I have seen several solutions to the combination issue which uses nested loops. I'd like to steer clear of 13 nested loops (but this is my best bet at the moment). I'm kind of at a loss on how to generate every unique combination in in each column.

I'm not sure if that makes enough sense for you guys. I was hoping someone could at least point me in the right direction with a recursive algorithm. I'd like to make it dynamic enough to take varying numbers of columns and rows.

Thanks for any help you guys can give me.

Answer 1

I needed this myself several times and finally built it.

I believe the code scales for any total number of columns and any number of distinct values within columns (e.g. each column can contain any number of values)

It assumes all values in each column are unique (if this is not true, you will get duplicate rows)

It assumes you want to cross-join output based on whatever cells you have currently selected (make sure you select them all)

It assumes you want the output to start one column after the current selection.

How it works (briefly): first for each column and for each row: It calculates the number of total rows needed to support all combos in N columns (items in column 1 * items in column 2 ... * items in column N)

second for each column: Based on the total combos, and the total combos of the previous columns it calculates two loops.

ValueCycles (how many times you have to cycle through all the values in the current column) ValueRepeats (how many times to repeat each value in the column consecutively)

Sub sub_CrossJoin()

Dim rg_Selection As Range
Dim rg_Col As Range
Dim rg_Row As Range
Dim rg_Cell As Range
Dim rg_DestinationCol As Range
Dim rg_DestinationCell As Range
Dim int_PriorCombos As Long
Dim int_TotalCombos As Long
Dim int_ValueRowCount As Long
Dim int_ValueRepeats As Long
Dim int_ValueRepeater As Long
Dim int_ValueCycles As Long
Dim int_ValueCycler As Long

int_TotalCombos = 1
int_PriorCombos = 1
int_ValueRowCount = 0
int_ValueCycler = 0
int_ValueRepeater = 0

Set rg_Selection = Selection
Set rg_DestinationCol = rg_Selection.Cells(1, 1)
Set rg_DestinationCol = rg_DestinationCol.Offset(0, rg_Selection.Columns.Count)

'get total combos
For Each rg_Col In rg_Selection.Columns
    int_ValueRowCount = 0
    For Each rg_Row In rg_Col.Cells
        If rg_Row.Value = "" Then
            Exit For
        End If
        int_ValueRowCount = int_ValueRowCount + 1
    Next rg_Row
    int_TotalCombos = int_TotalCombos * int_ValueRowCount
Next rg_Col

int_ValueRowCount = 0

'for each column, calculate the repeats needed for each row value and then populate the destination
For Each rg_Col In rg_Selection.Columns
    int_ValueRowCount = 0
    For Each rg_Row In rg_Col.Cells
        If rg_Row.Value = "" Then
            Exit For
        End If
        int_ValueRowCount = int_ValueRowCount + 1
    Next rg_Row
    int_PriorCombos = int_PriorCombos * int_ValueRowCount
    int_ValueRepeats = int_TotalCombos / int_PriorCombos


    int_ValueCycles = (int_TotalCombos / int_ValueRepeats) / int_ValueRowCount
    int_ValueCycler = 0

    int_ValueRepeater = 0

    Set rg_DestinationCell = rg_DestinationCol

    For int_ValueCycler = 1 To int_ValueCycles
        For Each rg_Row In rg_Col.Cells
            If rg_Row.Value = "" Then
                Exit For
            End If

                For int_ValueRepeater = 1 To int_ValueRepeats
                    rg_DestinationCell.Value = rg_Row.Value
                    Set rg_DestinationCell = rg_DestinationCell.Offset(1, 0)
                Next int_ValueRepeater

        Next rg_Row
    Next int_ValueCycler

    Set rg_DestinationCol = rg_DestinationCol.Offset(0, 1)
Next rg_Col
End Sub