All possible combination

[arishy]

I have 4 one column tables
TableA from 1 to 20
TableB from 1 to 5
TableC from 1 to 7
TableD from 1 to 3

All these tables can EXPAND.

I need to create TableAB with all possible COMBINATIONA but no duplicates
Same for TableBC, TableCD.

In case you wonder I am trying to create test data to test Access Database

 

[Sam Mathai Chacko]

Just combinations, right? Not permutations?

 

[arishy]

Good question, Yes I meant permutation not combination because 2 & 3 is the not same as 3 & 2
These tables are indexes. I need x to the power of y in my case y is 2
Needless to say the resulting TableAB is two columns ...etc

Thank you for the correction....

 

[NARAYANK991]

Hi ,

There is an uploaded workbook available at this link :

http://www.excelforum.com/excel-pro...09158-list-all-permutations-of-two-lists.html

Of course , if by duplicates , you mean combinations such as 1 & 1 , 2 & 2 ,... , then the workbook does not eliminate duplicates ; you can probably modify the code to do this.

Narayan

 

[Hui]

Arishy
You may also want to have a look here:
https://sites.google.com/site/e90e50fx/home/combinatorics-using-excel-formulas-and-examples

 

[arishy]

Thanks for the link....I modified it and it worked.
One correction about "my" math....The permutation is the product and not the Exp.!!!!!!
So, for TableA of 3 and TableB of 5 I get 15 possible permutations and not x to the power of y !!!!!

 

[arishy]

Here what I did

Sub Process()
Dim TableARow As Long
Dim TableBRow As Long
Dim OutputRow As Long
Sheet1.Cells.Clear 'Get rid of any previous values
OutputRow = 1
Sheet1.Cells(OutputRow, "A") = "TableA"
Sheet1.Cells(OutputRow, "B") = "TableB"
'
TableARow = 2 ' Allow for Headings in Row 1
Do Until Sheet2.Cells(TableARow, "A") = ""
TableBRow = 2 ' Allow for Headings in Row 1
Do Until Sheet3.Cells(TableBRow, "A") = ""
OutputRow = OutputRow + 1
Sheet1.Cells(OutputRow, "A") = Sheet2.Cells(TableARow, "A")
Sheet1.Cells(OutputRow, "B") = Sheet3.Cells(TableBRow, "A")
'
TableBRow = TableBRow + 1
Loop
TableARow = TableARow + 1
Loop
End Sub

 

[dan_l]

Just a word: I've done a similar project at least a half dozen times over the last 2 years or so. Doing it with excel works, but the absolute fastest way to get it done is access.