Cartesian Product 8 sets

[ExcelAverage]

Hi,

I know it's normally for two sets only, but how about as many as 8 sets. is that possible or any other way to perform that?

Thanks for all your help

 

[gsistek]

How big are the sets?
Are they variable in size?

 

[ExcelAverage]

Hi Gsistek,
Thanks for your reply. I think it's quite big. One set contains 10 items and the least has two items. I have 8 sets of data. Please see this table.

Single	Route	Bal	Trans	En	KL	W	E
Dual	Analyze	Ali	Ods	Es	Na	9	H
Four	Cascade	Both	TransOds	Fr
	None	None	None	De
				Tr
				Si
				Fi
				Za
				Ru
				Pr

<tbody>
</tbody>

First combination would be - Single, Route, Bal, Trans, En, Kl, W, E
2. Single, Analyze, Bal, Trans, En, Kl, W, E and so on...

Combinations from left to right with column A as the base. So i want all the combinations for Single, Dual and Four.

Thanks again... Let me know if I missed something.

Thanks

 

[gsistek]

How do you want the output? Would a list of all combinations on a separate sheet work (with each element in a different column)? It would give you a table of 15,360 rows and 8 columns.

 

[ExcelAverage]

Wow that was fast. Thanks Gsistek.
First, how did you come up with 15360? I knew I was wrong as i was able to arrive at only 5000+ records using concatenation. That is changing cell reference every time.

To make my spreadsheet flexible, as the data changes each time (columns and rows elements), I need the output be written at least on the 30th row of my excel. That's safe enough. Will you do this in excel? I found a macro program yesterday but that is good for 3 data with 3 elements each. I can't edit to make it suited for 8 sets with different element.

Thanks for your time...

 

[gsistek]

Wow that was fast. Thanks Gsistek.
First, how did you come up with 15360? I knew I was wrong as i was able to arrive at only 5000+ records using concatenation. That is changing cell reference every time.

To make my spreadsheet flexible, as the data changes each time (columns and rows elements), I need the output be written at least on the 30th row of my excel. That's safe enough. Will you do this in excel? I found a macro program yesterday but that is good for 3 data with 3 elements each. I can't edit to make it suited for 8 sets with different element.

Thanks for your time...

Try this out:

Sub CartProd8()
    Dim CartSize(1 To 8) As Long
    Dim I1 As Long, I2 As Long, I3 As Long, I4 As Long, _
        I5 As Long, I6 As Long, I7 As Long, I8 As Long
    Dim CurrentRow As Long, TotalRows As Long
'Disable Screen Updating (for speed)
    Application.ScreenUpdating = False
'Clear Current List
    Range(Cells(30, 1), Cells(Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row, 8)).Delete (xlShiftUp)
'Populate Size Array
    For I1 = 1 To 8
        CartSize(I1) = Cells(29, I1).End(xlUp).Row
    Next I1
'Determine Finished Size
    TotalRows = CartSize(1) * CartSize(2) * CartSize(3) * CartSize(4) * _
        CartSize(5) * CartSize(6) * CartSize(7) * CartSize(8)
'Start at Row 30
    CurrentRow = 30
'Copy Data - Loop Through Every Possibility
    For I1 = 1 To CartSize(1)
        For I2 = 1 To CartSize(2)
            For I3 = 1 To CartSize(3)
                For I4 = 1 To CartSize(4)
                    For I5 = 1 To CartSize(5)
                        For I6 = 1 To CartSize(6)
                            For I7 = 1 To CartSize(7)
                                For I8 = 1 To CartSize(8)
                                    Cells(CurrentRow, 1) = Cells(I1, 1)
                                    Cells(CurrentRow, 2) = Cells(I2, 2)
                                    Cells(CurrentRow, 3) = Cells(I3, 3)
                                    Cells(CurrentRow, 4) = Cells(I4, 4)
                                    Cells(CurrentRow, 5) = Cells(I5, 5)
                                    Cells(CurrentRow, 6) = Cells(I6, 6)
                                    Cells(CurrentRow, 7) = Cells(I7, 7)
                                    Cells(CurrentRow, 8) = Cells(I8, 8)
                                    CurrentRow = CurrentRow + 1
'Display Status
                                    Application.StatusBar = CurrentRow - 30 & "/" & TotalRows & _
                                        "  " & Format((CurrentRow - 30) / TotalRows, "0.0%")
                                Next I8
                            Next I7
                        Next I6
                    Next I5
                Next I4
            Next I3
        Next I2
    Next I1
'Clear StatusBar
    Application.StatusBar = ""
'Enable Screen Updating
    Application.ScreenUpdating = True
End Sub

 

[ExcelAverage]

Thanks very much for the time and effort. such a long code to build.
Surely, I will try this.

I so envy you for having this skills in VB codes.

Thanks again gsistek.

 

[thenapolitan]

I know this is an old thread - wondering if anyone has tried doing this for X number of sets? (possibly using recursion?) I'm looking to do this.

 

[gsistek]

I've toyed around with that. I just haven't had the time to sit down and write it out. I will definitely post it if I come up with something.

 

[thenapolitan]

I've toyed around with that. I just haven't had the time to sit down and write it out. I will definitely post it if I come up with something.

Looks like we may have come up with something here: http://www.mrexcel.com/forum/excel-...lications-make-combinations-dynamic-data.html

Works great for my application - only had to modify slightly to fit into what I needed to do.