Generate Combinations in Excel

Question

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?

Answer 1

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 the CROSSJOIN 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 use CROSSJOIN(table1, table2).

Answer 2

If you haven't seen this link already, it could be of use. There are some formula examples down the bottom. https://stackoverflow.com/questions/10692653/excel-vba-to-create-every-possible-combination-of-a-range

However, the CROSSJOIN answer above is a much cleaner way.