Generating a table of all possible combinations

[abdulbasitb]

Hi everyone,

Is there a quick way of generating a table or pivot-table showing all possible combinations of prescribed options.
The example file shows what I'm after

INPUT

Furniture Material Condition Finishing color Chair Wooden New White Table Plastic Used Red Steel Black <tbody> </tbody>

<tbody>
</tbody>

OUTPUT

Furniture	Material	Condition	Finishing color	Cost
Chair	Wooden	New	White
Chair	Wooden	New	Red
Chair	Wooden	New	Black
Chair	Wooden	Used	White
Chair	Wooden	Used	Red
Chair	Wooden	Used	Black
Chair	Plastic	New	White
Chair	Plastic	New	Red
Chair	Plastic	New	Black
Chair	Plastic	Used	White
Chair	Plastic	Used	Red
Chair	Plastic	Used	Black
Chair	Steel	New	White
Chair	Steel	New	Red
Chair	Steel	New	Black
Chair	Steel	Used	White
Chair	Steel	Used	Red
Chair	Steel	Used	Black
Table	Wooden	New	White
Table	Wooden	New	Red
Table	Wooden	New	Black
Table	Wooden	Used	White
Table	Wooden	Used	Red
Table	Wooden	Used	Black
Table	Plastic	New	White
Table	Plastic	New	Red
Table	Plastic	New	Black
Table	Plastic	Used	White
Table	Plastic	Used	Red
Table	Plastic	Used	Black
Table	Steel	New	White
Table	Steel	New	Red
Table	Steel	New	Black
Table	Steel	Used	White
Table	Steel	Used	Red
Table	Steel	Used	Black

<tbody>
</tbody>

 

[Teeroy]

Hi and welcome to the forum.

Pivot tables are about reducing data, not expanding it, so they won't help you.

A relatively simple approach is to use VBA. In a standard code module copy and paste the following code; it assumes that the input data is in columns A-D on "Sheet1", starting at A1 and writes the output to "Sheet2".

Sub expand_Combinations()
Dim iEndCol(1 To 4) As Integer
Dim sOutput() As String
Dim lNumberCombinations As Long
'Get column Sizes
lNumberCombinations = 1
With Sheets("Sheet1")
    For i = 1 To 4
        iEndCol(i) = .Cells(Rows.Count, i).End(xlUp).Row
        lNumberCombinations = lNumberCombinations * (iEndCol(i) - 1)
    Next i
    ReDim sOutput(1 To lNumberCombinations)
    'set Temp Arrays
    vTempCol1 = .Range("A1:A" & iEndCol(1))
    vTempCol2 = .Range("B1:B" & iEndCol(2))
    vTempCol3 = .Range("C1:C" & iEndCol(3))
    vTempCol4 = .Range("D1:D" & iEndCol(4))
End With
'set counter
Count = 0
'count through all combinations and generate strings
For i = 2 To iEndCol(1)
    For j = 2 To iEndCol(2)
        For k = 2 To iEndCol(3)
            For m = 2 To iEndCol(4)
                Count = Count + 1
                sOutput(Count) = vTempCol1(i, 1) & "," & vTempCol2(j, 1) & "," & vTempCol3(k, 1) & "," & vTempCol4(m, 1)
            Next m
        Next k
    Next j
Next i
'Write strings to Sheet2
With Sheets("Sheet2")
    .Range("A1").Resize(1, 4).Value = Sheets("Sheet1").Range("A1").Resize(1, 4).Value
    For i = 1 To Count
        .Cells(i + 1, 1).Resize(1, 4).Value = Split(sOutput(i), ",")
    Next i
End With
End Sub

Remember that VBA changes usually cannot be undone so test on a COPY of your workbook first.

 

[abdulbasitb]

Thanks. This is the most active forum I've seen in my life. I'm comparing amongst all social, professional and hobby forums I've ever visited.

 

[Fazza]

If you do want a pivot table approach, make a Cartesian product query from 4 source tables. Such as create normal defined names for the source data of "Furniture", "Material", "Condition" & "Color". Save the file. From a new file ALT-D-P and choose external data at the first screen. Follow the wizard. Choose your file and then all four tables as the source data. 'OK' to acknowledge whatever message you see and then see the results dataset in MS Query. 'Open door' icon to exit MS Query & finish the pivot table.

Though it is probably better to have a query table instead of pivot table. Basic difference is instead of starting ALT-D-P start ALT-D-D-N

regards

 

[Teeroy]

If you do want a pivot table approach, make a Cartesian product query from 4 source tables. Such as create normal defined names for the source data of "Furniture", "Material", "Condition" & "Color". Save the file. From a new file ALT-D-P and choose external data at the first screen. Follow the wizard. Choose your file and then all four tables as the source data. 'OK' to acknowledge whatever message you see and then see the results dataset in MS Query. 'Open door' icon to exit MS Query & finish the pivot table.

Though it is probably better to have a query table instead of pivot table. Basic difference is instead of starting ALT-D-P start ALT-D-D-N

regards

Very nice Fazza. I didn't know that could be done without an SQL server.

 

[Fazza]

SQL in Excel is great. Custom pivot tables, query tables, recordsets via VBA, database interaction. Data can come from all sorts of sources, and with SQL power can do some great stuff. Can do other stuff too - like UPDATE queries to worksheets - that isn't the best.

 

[Simaa]

That code is great, it works. But the problem is that if I have more than 1.048.576 combinations, it wont generate, because that is the maximum number rows. Could Teeroy please write a Vba code without that limitations? To continue in F column?