All combinations of multiple columns of values?

[rjvam]

I have four columns of values. I am looking for a solution - be it formula, macro, or anything else! - to produce a list of all combinations of those four columns of values, separated by a character of my choosing.

For example:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Horse[/TD]
[TD]Red[/TD]
[TD]20"[/TD]
[TD]Web[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Pig[/TD]
[TD]Blue[/TD]
[TD]30"[/TD]
[TD]Retail[/TD]
[/TR]
</tbody>[/TABLE]

I would want this solution to result in a list - on another sheet or document, the end destination doesn't matter to me - that would produce in this:

Horse-Red-20"-Web
Horse-Red-20"-Retail
Horse-Red-30"-Web
Horse-Red-30"-Retail
Horse-Blue-20"-Web
Horse-Blue-20"-Retail
Horse-Blue-30"-Web
Horse-Blue-30"-Retail
Pig-Red-20"-Web

And so on!

I tried to find an existing thread with this issue, but failed.

I am using Excel 2007. Thank you for your help and attention!

 

[Takae]

If you have more colum(s), it needs to modify.
Result will be in column F in same sheet.
Hope this helps.

Sub sample_rjvam()
Dim x, buf As String, C, keys
Dim i As Long, j As Long, k As Long, l As Long, LastR As Long
Set x = CreateObject("Scripting.Dictionary")
LastR = Cells(Rows.count, 1).End(xlUp).row
For Each C In Range(Range("A2"), Cells(LastR, 1))
    For i = 2 To LastR
        For j = 2 To LastR
            For k = 2 To LastR
                For l = 2 To LastR
                    buf = C & Cells(j, 2).Value & Cells(k, 3).Value & Cells(l, 4).Value
                        If Not x.Exists(buf) Then
                            x.add buf, buf
                        End If
                Next
            Next
        Next
    Next
Next
keys = x.keys
For i = 0 To x.count - 1
    Cells(i + 2, 6) = keys(i)
Next i
Set x = Nothing
End Sub

 

[bosco_yip]

A formula way, F1 copy down :

=IF(ROWS(A$1:A1)<17,IF(INT((ROWS($A$1:A1)-1)/8),A$2,A$1)&"-"&IF(MOD(INT((ROWS($A$1:A5)-1)/4)+1,2),B$2,B$1)&"-"&IF(MOD(INT((ROWS($A$1:A3)-1)/2)+1,2),C$2,C$1)&"-"&IF(MOD(ROWS($A$1:A2),2),D$2,D$1),"")

Regards

 

[pgc01]

Hi

A solution for a more general problem: any number of columns, columns may have different number of rows:


http://www.mrexcel.com/forum/excel-questions/469979-permutation-excel.html#9

 

[rjvam]

Thank you all for your help! I appreciate it, and I appreciate multiple solutions as well.

Have a great day - you all have helped mine be better!