Combinations of a text list

[caladanbrood]

Hi all,

I have been searching on various sites for a while for a solution to this problem and I keep thinking I've found what I need but never quite finding the right thing.

I have a list of people, and what I need to get is all possible combinations of these people. I can do this manually, however the list may be expanded or changed, and I don't want to have to do it each time. Unfortunately macros are not a possibility.

So the list I have is:
Ash
Craig
Jon
Claire

And the results I need are:
Ash
Craig
Jon
Claire
Ash; Craig
Ash; Jon
Ash; Claire
Craig; Jon
Craig; Claire
Jon; Claire
Ash; Craig; Jon
...
etc all the way down to
...
Ash; Craig; Jon; Claire

This would then be a named range which can be used in a drop-down box elsewhere.

This is beyond my equation-fu, but I was hoping someone may have come across this before. There will never be more than 6 people in this list.

If this is not possible then I will have to find some alternative, but at least I would be put out of my misery!!!

Thanks in advance, you guys are always incredible!

 

[SirJB7]

Hi, caladanbrood!

Check this:
https://sites.google.com/site/e90e50fx/home/combinatorics-using-excel-formulas-and-examples

I've done something here at this website for up to 4 or 5 elements per list (if I don't remember bad) but still unable to find it; surely it's lost among the old forums software issues... aka, the twilight zone.

Regards!

 

[caladanbrood]

It didn't enjoy using text, but a simple vlookup substitution for numbers at each end did the job, thanks SirJB7.

It got horribly large even when using 5 names, so I'll just have to set the maximum as 4!!

Cheers,
Jon

 

[SirJB7]

Hi, caladanbrood!

Give a look at the uploaded file. It's like that I can't find here at this site, but it isn't finished yet. Formulas for columns D:J (in fact E:J as D is a dummy for keeping formulas -future- easier) aren't yet written. I'll try to do when 3 things happen: find the time, get the inspiration, open a Carslberg. Will be back then, in the meanwhile play a bit with it, if you wish to.

Regards!