Permutation Builder

[Stephen]

I have a set of columns (undefined number) with an undefined number of
parameters for each. I am looking for a way to come up with every
combination of those columns / Parameters.

For Example:

Direction TradeType
Buy Spot
Sell Fwd
Swap

Desired:

Buy Spot
Sell Spot
Buy Fwd
Sell Fwd
Buy Swap
Sell Swap

Any thoughts on how this can be done?

Thanks

 

[Mike H]

Stephen,

With your 2 lists in columns A & B this outputs to column C

Right click the sheet tab, view code and paste it in:-

Sub stance_abuse()
lastrowa = Cells(Rows.Count, "A").End(xlUp).Row
lastrowb = Cells(Rows.Count, "B").End(xlUp).Row
For x = 1 To lastrowa
For Y = 1 To lastrowb
Cells(x + z, 3).Value = Cells(x, 1).Value & " - " & Cells(Y, 2).Value
If Y < lastrowb Then z = z + 1
Next
Next
End Sub

Mike

 

[Bernie Deitrick]

Stephen,

With your data table starting in cell A1, with labels in row 1, and all possible values in the
columns (without blanks between), run the macro below. This will keep the values in separate cells,
but they could just as easily be combined. (Unquote the quoted code if you want the values in one
cell - in column A.)

HTH,
Bernie
MS Excel MVP


Sub ListCombins()
Dim i As Integer
Dim j As Integer
Dim iMax As Integer
Dim jMax As Integer
Dim n As Long

iMax = Cells(Rows.Count, 1).End(xlUp).Row
jMax = Cells(Rows.Count, 2).End(xlUp).Row

n = 10
For i = 2 To iMax
For j = 2 To jMax
Cells(n, 1).Value = Cells(i, 1).Value
Cells(n, 2).Value = Cells(j, 2).Value
' Cells(n, 1).Value = Cells(n, 1).Value & " " & Cells(n, 2).Value
' Cells(n, 2).Clear
n = n + 1
Next j
Next i
End Sub

 

[Stephen]

Thank you very much, this is perfect

Stephen

Stephen,

With your data table starting in cell A1, with labels in row 1, and all possible values in the
columns (without blanks between), run the macro below. This will keep the values in separate cells,
but they could just as easily be combined. (Unquote the quoted code if you want the values in one
cell - in column A.)

HTH,
Bernie
MS Excel MVP


Sub ListCombins()
Dim i As Integer
Dim j As Integer
Dim iMax As Integer
Dim jMax As Integer
Dim n As Long

iMax = Cells(Rows.Count, 1).End(xlUp).Row
jMax = Cells(Rows.Count, 2).End(xlUp).Row

n = 10
For i = 2 To iMax
For j = 2 To jMax
Cells(n, 1).Value = Cells(i, 1).Value
Cells(n, 2).Value = Cells(j, 2).Value
' Cells(n, 1).Value = Cells(n, 1).Value & " " & Cells(n, 2).Value
' Cells(n, 2).Clear
n = n + 1
Next j
Next i
End Sub