Generate a table of all possible combinations

I'm wondering how to do this in Excel.

I have a table with changing values. Lets say its 3 rows by 6 columns.

Joe	Jill	Jim	Jack	Jess	Jake
Sally	Sue	Steve	Stu	Sam	Scott
Alan	Adam	Alex	Archie	Ana	Arthur

I'd now like to use this as data to generate all possible combinations of 6, with the caveat of keeping the same column position.

So we already have 3 combinations, as above, but the next few might look like
Joe, Sue, Jim, Jack, Jess, Jake
Joe, Jill, Steve, Jack, Jess, Jake
Joe, Jill, Jim, Stu, Jess, Jake
.....
Joe, Sue, Steve, Jack, Jess, Jake
....
Sally, Jill, Steve, Stu, Sam, Scott
....
Joe, Sue, Jim, Archie, Sam, Arthur
etc, etc.

The column position always needs to stay the same.
So Sally always appears first, and Sue always appears 2nd, Jess always appears 5th, Arthur always appears 6th.

Any ideas on how to do this in Excel?

If coding this, I'd load up an array then progress through the rows for each column. Any other methods? Trying to make the method non-programmer friendly for later modification by non-IT folks. (so I don't have to own it.)

Many thanks,

Val

Hi

How about

Please Login or Register  to view this content.

rylo

Awesome, thanks!