All possible combinations from 5 columns with no duplicates, results kept in columns.

[andrew_milonavic]

Hi,

I have 5 lists, each in a column, and would like to return the possible combinations keeping the output in individual columns. No duplicates, and reverse order would be considered a duplicate, such as Cat Car or Car Cat.

An example with just three columns. Results on the right.

Cat	Car	Pizza		Cat	Car	Pizza
Dog	Truck	Pasta		Cat	Car	Pasta
Zebra	Van	Apple		Cat	Car	Apple
Mouse	Plane	Orange		Cat	Car	Orange
Elephant	Boat	Rice		Cat	Car	Rice

Possible with a formula?

Thanks

Andrew

 

[jkpieterse]

Perhaps an old newsletter of mine helps: jkp-ads.com Newsletter

 

[andrew_milonavic]

Hi @jkpieterse,

Thanks for the suggestion but I'd rather have a formula solution and avoid pivot tables if possible.

Thanks

Andrew

 

[jkpieterse]

I wonder why I get that response so often when I suggest a pivottable.
I can't think of a formula that will do as you ask. EVen if you succeed, I'm positive it will be one heck of a complicated (and slooooowwww) formula.
With a pivottable this is peanuts.

 

[andrew_milonavic]

Hi @jkpieterse ,

Probably due to a lack of understanding and familiarity.

With regards to the speed I only need to run it a few times periodically. The results are going to copied as values.

Thanks

Andrew

 

[jkpieterse]

Even in that case, the pivottable is quite easy to setup, just follow the instructions in the newsletter for 5 tables instead of 2.