VBA to Make Combinations with Dynamic Data

[thenapolitan]

Hey Guys,

I've been racking my brain and can't seem to figure out the coding logic for what I'm doing. Basically, what I want to do is get all the combinations for a subset of parts to eventually come up with a gap/tolerance stack equation (not automated, yet).

I ask the user how many unique parts exist (in the example below, the user entered 3, but could be any number). I then ask how many duplicates of each (in this case 3 of part 1, 3 of part 2, and 2 of part 3, but again, these numbers could vary). It populates TABLE 1 below.

I populate a 2D array like this, but I'm not sure this is really how it should be done.
[TABLE="class: grid, width: 192"]
<tbody>[TR]
[TD="width: 64"]part[/TD]
[TD="width: 64"]# of parts[/TD]
[TD="width: 64"]dims[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

Now, I want to make a matrix to show all combinations of those parts. There should be 18 combinations (3*3*2). And I want the matrix to look like TABLE 2.

I just can't wrap my head around how to get this to work for any number of unique parts, with any number of duplicates and any number of dimensions.

Any ideas?

Thanks,

Chris



TABLE 1
[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD="align: right"]Part:[/TD]
[TD]1[/TD]
[TD][/TD]
[TD="align: right"]Number of Parts:[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]Number of Dimensions:[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]Dim 1[/TD]
[TD="align: center"]Dim 2[/TD]
[TD="align: center"]Dim 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Part 1 of 1:[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]113[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Part 2 of 1:[/TD]
[TD="align: right"]121[/TD]
[TD="align: right"]122[/TD]
[TD="align: right"]123[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Part 3 of 1:[/TD]
[TD="align: right"]131[/TD]
[TD="align: right"]132[/TD]
[TD="align: right"]133[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Part:[/TD]
[TD]2[/TD]
[TD][/TD]
[TD="align: right"]Number of Parts:[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]Number of Dimensions:[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]Dim 1[/TD]
[TD="align: center"]Dim 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Part 1 of 2:[/TD]
[TD="align: right"]211[/TD]
[TD="align: right"]212[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Part 2 of 2:[/TD]
[TD="align: right"]221[/TD]
[TD="align: right"]222[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Part 3 of 2:[/TD]
[TD="align: right"]231[/TD]
[TD="align: right"]232[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Part:[/TD]
[TD]3[/TD]
[TD][/TD]
[TD="align: right"]Number of Parts:[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]Number of Dimensions:[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]Dim 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Part 1 of 3:[/TD]
[TD="align: right"]311[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Part 2 of 3:[/TD]
[TD="align: right"]321[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



TABLE 2
[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD]Combo #[/TD]
[TD]Part 1[/TD]
[TD]Part 2[/TD]
[TD]Part 3[/TD]
[TD]Part 1 Dim 1[/TD]
[TD]Part 1 Dim 2[/TD]
[TD]Part 1 Dim 3[/TD]
[TD]Part 2 Dim 1[/TD]
[TD]Part 2 Dim 2[/TD]
[TD]Part 3 Dim 1[/TD]
[TD]Gap Formula[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]113[/TD]
[TD="align: right"]211[/TD]
[TD="align: right"]212[/TD]
[TD="align: right"]311[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]113[/TD]
[TD="align: right"]211[/TD]
[TD="align: right"]212[/TD]
[TD="align: right"]321[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]113[/TD]
[TD="align: right"]221[/TD]
[TD="align: right"]222[/TD]
[TD="align: right"]311[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]113[/TD]
[TD="align: right"]221[/TD]
[TD="align: right"]222[/TD]
[TD="align: right"]321[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]113[/TD]
[TD="align: right"]231[/TD]
[TD="align: right"]232[/TD]
[TD="align: right"]311[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]113[/TD]
[TD="align: right"]231[/TD]
[TD="align: right"]232[/TD]
[TD="align: right"]321[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]121[/TD]
[TD="align: right"]122[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]211[/TD]
[TD="align: right"]212[/TD]
[TD="align: right"]311[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]121[/TD]
[TD="align: right"]122[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]211[/TD]
[TD="align: right"]212[/TD]
[TD="align: right"]321[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]121[/TD]
[TD="align: right"]122[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]221[/TD]
[TD="align: right"]222[/TD]
[TD="align: right"]311[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]121[/TD]
[TD="align: right"]122[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]221[/TD]
[TD="align: right"]222[/TD]
[TD="align: right"]321[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]121[/TD]
[TD="align: right"]122[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]231[/TD]
[TD="align: right"]232[/TD]
[TD="align: right"]311[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]121[/TD]
[TD="align: right"]122[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]231[/TD]
[TD="align: right"]232[/TD]
[TD="align: right"]321[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]131[/TD]
[TD="align: right"]132[/TD]
[TD="align: right"]133[/TD]
[TD="align: right"]211[/TD]
[TD="align: right"]212[/TD]
[TD="align: right"]311[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]131[/TD]
[TD="align: right"]132[/TD]
[TD="align: right"]133[/TD]
[TD="align: right"]211[/TD]
[TD="align: right"]212[/TD]
[TD="align: right"]321[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]131[/TD]
[TD="align: right"]132[/TD]
[TD="align: right"]133[/TD]
[TD="align: right"]221[/TD]
[TD="align: right"]222[/TD]
[TD="align: right"]311[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]131[/TD]
[TD="align: right"]132[/TD]
[TD="align: right"]133[/TD]
[TD="align: right"]221[/TD]
[TD="align: right"]222[/TD]
[TD="align: right"]321[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]131[/TD]
[TD="align: right"]132[/TD]
[TD="align: right"]133[/TD]
[TD="align: right"]231[/TD]
[TD="align: right"]232[/TD]
[TD="align: right"]311[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]131[/TD]
[TD="align: right"]132[/TD]
[TD="align: right"]133[/TD]
[TD="align: right"]231[/TD]
[TD="align: right"]232[/TD]
[TD="align: right"]321[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[L. Howard]

Mostly, I don't understand what you are trying to do. My fault, not yours, I'm not a numbers guy.

I am wondering if a Cartesian Permutation worksheet would be of help?
One is fairly available on a search, I believe, or I can post a link to my example.

I enter you little grid...

1 3 3
2 3 2
3 2 1


And it produced this result in a split second. Where this is a concatenated end result with duplicates remove, if any.

1 3 3
1 3 2
1 3 1
1 2 3
1 2 2
1 2 1
2 3 3
2 3 2
2 3 1
2 2 3
2 2 2
2 2 1
3 3 3
3 3 2
3 3 1
3 2 3
3 2 2
3 2 1

The Table 1 & 2 are beyond my thought process.

Howard

 

[thenapolitan]

Hey L. Howard, thanks for the reply. I'll take a look at the Cartesian Permutation, but let me try to simplify a little. I think it's just too complicated with those "dimensions" in there.

Basically, for that example figure I have 3 unique mechanical parts that fit together in some way (but could be any number of parts that the user enters):

• part 1 has 3 duplicates (don't worry about dimensions)
  • aka, we got 3 of these parts made at a manufacturer, but the user should be able to enter any number of duplicates, say, if we had 15 made or something
• part 2 has 3 duplicates
• part 3 has 2 duplicates

So - the total number of combinations is 18 (3*3*2). Below shows the combo number and the actual combination of parts that would fit together. It actually reminds me a little of binary counting, but varies depending on the number of duplicates the user enters.

Combo #1 would look at Part 1's first duplicate, Part 2's first duplicate, Part 3's first duplicate.
Combo #2 would look at Part 1's first duplicate, Part 2's first duplicate, Part 3's second duplicate.
Combo #3 would look at Part 1's first duplicate, Part 2's second duplicate, Part 3's first duplicate.

...and so on. The biggest problem I am facing is that I want to make this work for any number of parts with any number of duplicates.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Combo #[/TD]
[TD]Part 1[/TD]
[TD]Part 2[/TD]
[TD]Part 3[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

 

[thenapolitan]

Ok L. Howard! I just found a Cartesian Product thing! Made it work with the set of 3 parts. WORKS GREAT! This is very close to exactly what I want. The only problem is I want to make it work for a dynamic number of parts with a dynamic number of duplicates (I'm thinking to use recursion, but I have no idea how to made a function that will do that yet). Will post updates as I come up with them. See the code below that works for 3 parts (modified slightly from Creating a cartesian product in Excel with a Visual Basic macro - blog @ tootricky.co.uk):

Sub cartesianproduct()
    Dim startrange As Range
    
    range1 = Application.InputBox(Prompt:="Please Select First Range", Type:=8)
    range2 = Application.InputBox(Prompt:="Please Select Second Range", Type:=8)
    range3 = Application.InputBox(Prompt:="Please Select Third Range", Type:=8)
    Set startrange = Application.InputBox(Prompt:="Please select where you want to put it", Type:=8)
    
    array1 = [range1]
    array2 = [range2]
    array3 = [range3]
    
    startrange.Select
    For i = 1 To UBound(array1)
        For x = 1 To UBound(array2)
            For y = 1 To UBound(array3)
                Z = Z + 1
                ActiveCell.Offset(Z, 0).Value = array1(i, 1)
                ActiveCell.Offset(Z, 1).Value = array2(x, 1)
                ActiveCell.Offset(Z, 2).Value = array2(y, 1)
            Next y
        Next
    Next
End Sub

 

[L. Howard]

Great. Looks like you are making good progress.

Howard

 

[shg]

There's a workbook with formula- and vba-based methods for Cartesian products at https://app.box.com/s/47b28f19d794b25511be

 

[thenapolitan]

There's a workbook with formula- and vba-based methods for Cartesian products at https://app.box.com/s/47b28f19d794b25511be

Works well, but looking to find code that will work with X sets of data. I may do an analysis on 2 sets, or 15 sets or more.

 

[shg]

It will works with as many as you enter, with up to 1 million results.

 

[thenapolitan]

It will works with as many as you enter, with up to 1 million results.

It looks like it can only have 4 categories (in this example "appetizer", "main course", "rice", "dessert"). Each category is a "set" for me. I want to be able allow the user to choose how many "sets" they want. Will the spreadsheet do this?

 

[shg]

Did you read this part of the instructions?

To increase the number of columns, insert columns in the middle of the table and copy the columns from the left or right to cover.