Excel – Multiple Columns, Different Combinations

Question

I have 4 columns with 4 different parameters, if that's the right word. Each parameter has about 3-5 variables. What I want to do is I want to create ALL possible combinations of the 4 different parameters while maintaining the different columns. So let's say I have the following as an example:

**Column A | Column B | Column C**

Chicago  | Football | Red 

New York | Soccer   | White

Seattle  | Hockey   | Blue

What I want is to have, all the combinations I could get out of those columns, which I can illustrate as:

**Column A | Column B | Column C**

New York | Football | Blue 

New York | Football | Red

New York | Football | White

New York | Soccer   | Blue 

New York | Soccer   | Red

New York | Soccer   | White

New York | Hockey   | Blue 

New York | Hockey   | Red

New York | Hockey   | White

Chicago  | Football | Blue 

Chicago  | Football | Red

Chicago  | Football | White...

and so on.

Answer 1

Edit with Office 365 we can use:

=LET(
    rng,A1:C3,
    rw,ROWS(rng),
    clm,COLUMNS(rng),
    SORT(MAKEARRAY(rw^clm,clm,LAMBDA(a,b,INDEX(rng,INT(MOD(a,rw^b)/(rw^b/rw))+1,b))),SEQUENCE(,clm)))

Original vba answer.

This will give you every combination possible:

Function fifth(arr() As Variant) As Variant()

Dim temp() As Variant
Dim i As Long
Dim j As Long
Dim t As Long

ReDim temp(1 To (UBound(arr, 1)) ^ (UBound(arr, 2)), LBound(arr, 2) To UBound(arr, 2)) As Variant
For i = 1 To (UBound(arr, 1) ^ UBound(arr, 2))
    For j = 1 To UBound(arr, 2)
        t = Int((i Mod ((UBound(arr)) ^ j)) / (((UBound(arr)) ^ j) / (UBound(arr))))
        temp(i, j) = arr(t + 1, j)
    Next j
Next i

fifth = temp

End Function

You would call thus:

Sub ArrCombine()
Dim ws As Worksheet
Dim arr1() As Variant
Dim rsltarr() As Variant

Set ws = Sheets("Sheet1") 'Change to your sheet

arr1 = ws.Range(ws.Range("A1"), ws.Range("A1").End(xlToRight).End(xlDown)).Value

rsltarr = fifth(arr1)

ws.Range("A1").End(xlToRight).Offset(, 1).Resize(UBound(rsltarr, 1) - 1, UBound(rsltarr, 2)).Value = rsltarr

End Sub

It will output on the active sheet, this:

This will use any size range. The only restriction is that #ofRows ^ #ofColumns is not greater than the number of rows available on the sheet.