Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
    
Why not 30 30 100? – Raystafarian Feb 4 at 17:19
    
@Raystafarian Stink, and 30, 30, 60, I have edited. – Jonathan Mee Feb 4 at 17:22
    
Isn't 30 30 60 the same as 30 60 30? – Raystafarian Feb 4 at 17:23
    
@Raystafarian Right again, that should have read 30, 60, 60 – Jonathan Mee Feb 4 at 17:24
    
@Raystafarian wah! There is only 1x60. Whatever, I think it's right now. Thanks. – Jonathan Mee Feb 4 at 17:26
up vote 8 down vote accepted

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

share|improve this answer

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

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.