Excel - Generating the Cartesian product of three sets of numbers in each row

Question

I have data across a single row formatted as such:

Set 1 (Contains only one number) | Set 2 (Contains 1-6 numbers in unique cells) | Set 3 (Contains 1-6 in unique cells|

Example: [1] | [1] [2] [3] | [1] [5]

Output: 1 1 1, 1 1 5, 1 2 1, 1 2 5, 1 3 1, 1 3 5

Please explain the desired output. Even though you are using the word "permutations", I suspect that you might mean something else. — John Coleman, Mar 12 '17 at 18:35
For the above example: [1] | [1] [2] [3] | [1] [5] Output: 1 1 1, 1 1 5, 1 2 1, 1 2 5, 1 3 1, 1 3 5 — Ian ER, Mar 13 '17 at 18:47
actually, I suspect that you mean something more like a Cartesian product rather than either permutations or combinations. It would help if you edit your question to show the intended output. — John Coleman, Mar 13 '17 at 19:03
Edited. I haven't heard of this! Thanks for helping me articulate the question. — Ian ER, Mar 13 '17 at 19:10

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

Here is a VBA function which can handle the special case of 3 number sets:

Function CartesianProduct(nums1 As Range, nums2 As Range, nums3 As Range) As Variant
    Dim n As Long 'number of products
    Dim i As Long, j As Long, k As Long, r As Long
    Dim products As Variant
    
    n = nums1.Cells.Count * nums2.Cells.Count * nums3.Cells.Count
    ReDim products(1 To n, 1 To 3)
    For i = 1 To nums1.Cells.Count
        For j = 1 To nums2.Cells.Count
            For k = 1 To nums3.Cells.Count
                r = r + 1 'current row
                products(r, 1) = nums1.Cells(i)
                products(r, 2) = nums2.Cells(j)
                products(r, 3) = nums3.Cells(k)
            Next k
        Next j
    Next i
    CartesianProduct = products
End Function

This can be called from another VBA function or sub, or used directly as an array formula in the sheet:

In the above screenshot I selected the range A3:C8 (needing to determine its size ahead of time) entered the formula

=CartesianProduct(A1,B1:D1,E1:F1)

and then accept it as an array formula by entering it with Ctrl+Shift+Enter.

Once you get beyond three sets, things get a bit tricky since you can't hardwire in the necessary levels for a looping approach and would instead probably use a recursive approach, something along the lines of this answer: https://stackoverflow.com/a/31622856/4996248

Thanks for this, John. Works really well! Appreciate the help. Always great to learn something new as well. — Ian ER, Mar 17 '17 at 15:19

Sandra Rossi · Answer 2 · 2020-12-21T07:31:55.073

This is a function to do the cartesian product of any number of dimensions - The values of each dimension must be listed vertically, one dimension may have more than one column (see example below):

Function CartesianProduct(ParamArray range() As Variant) As Variant
    Dim n As Long 'number of products
    Dim total_dimensions As Long, i As Long, num_dim As Long, num_col As Long, max_cols As Long
    Dim dim_sizes As Variant
    Dim dim_counters As Variant
    Dim products As Variant
    
    ReDim dim_sizes(LBound(range) To UBound(range))
    ReDim dim_counters(LBound(range) To UBound(range))
    n = 1
    max_cols = 0
    For i = LBound(range) To UBound(range)
        dim_sizes(i) = range(i).Rows.Count
        max_cols = max_cols + range(i).Columns.Count
        n = n * dim_sizes(i)
        dim_counters(i) = 1
    Next
    ReDim products(1 To n, 1 To max_cols)
    For i = 1 To n
        carry_one = True
        num_col = max_cols
        For num_dim = UBound(range) To LBound(range) Step -1
            For j = range(num_dim).Columns.Count To 1 Step -1
                products(i, num_col) = range(num_dim).Cells(dim_counters(num_dim), j)
                num_col = num_col - 1
            Next j
            If carry_one = True Then
                dim_counters(num_dim) = dim_counters(num_dim) + 1
                If dim_counters(num_dim) > dim_sizes(num_dim) Then
                    dim_counters(num_dim) = 1
                    carry_one = True
                Else
                    carry_one = False
                End If
            End If
        Next num_dim
    Next i
    CartesianProduct = products
End Function

Example (note that the first dimension has two columns):

Excel - Generating the Cartesian product of three sets of numbers in each row

2 Answers2