I have an input row which contains the cells:
- 30
- 30
- 30
- 50
- 50
- 60
- 100
I want to generate all possible combinations of length 3 from this set. So I would want rows containing 3 populated cells and the elements of each row would contain a unique set independent of order. In other words I would not have a 50, 30, 50 and a 30, 50, 50.
I've done this by hand so this may not be perfect but I believe my output should be:
- 30, 30, 30
- 30, 30, 50
- 30, 30, 60
- 30, 30, 100
- 30, 50, 50
- 30, 50, 60
- 30, 50, 100
- 30, 60, 100
- 50, 50, 60
- 50, 50, 100
- 50, 60, 100
I know that I can generate these with VBA, but I'd like to know: Does Excel have any native commands for generating these rows?
Best Answer
You can use the
CROSSJOIN
function in Excel (PowerPivot add-in required for Excel 2010 & 2013 which you can download for free from Microsoft). Details about theCROSSJOIN
function can be found on the MSDN website.In sum, set up your base data as two (duplicate) tables
table1
&table2
making sure they have different column names, then useCROSSJOIN(table1, table2)
.