Generate Combinations in Excel

I have an input row which contains the cells:

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?

Jonathan Mee

Posted 2016-02-04T14:25:36.120

Reputation: 169

Why not 30 30 100? – Raystafarian – 2016-02-04T17:19:31.763

@Raystafarian Stink, and 30, 30, 60, I have edited. – Jonathan Mee – 2016-02-04T17:22:48.000

Isn't 30 30 60 the same as 30 60 30? – Raystafarian – 2016-02-04T17:23:25.063

@Raystafarian Right again, that should have read 30, 60, 60 – Jonathan Mee – 2016-02-04T17:24:57.830

@Raystafarian wah! There is only 1x60. Whatever, I think it's right now. Thanks. – Jonathan Mee – 2016-02-04T17:26:30.930

Answers

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).

Mekki MacAulay

Posted 2016-02-04T14:25:36.120

Reputation: 730

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.

Chris

Posted 2016-02-04T14:25:36.120

Reputation: 303