all combinations of uneven columns

[TXFI]

Howdy.

I'd need help getting all possible combinations of values from multiple columns. So something like this:

cell values in column A: fish, cat, dog, horse
cell values in column B: summer, winter
cell values in column C: rose, daisy, pansy
etc.

These go up to column K (=11 columns) and each column has between 2 and 20 cells with values, i.e. the number of cells in each column varies. As an example, valid answers using the three columns in the example above would be:

fish, summer, rose
dog, summer, pansy
horse, winter, daisy

but NOT e.g. "fish, rose, winter" or "dog, (blank), rose".

I guess this could be asked also as "How do I pick values from multiple columns of different length and produce a list of all combinations those values can produce?".

Thank you in advance.

 

[shg]

There's a workbook that will do this at http://www.box.net/shared/ubd28tissi

The number of combinations could get out of hand very quickly.

 

[TXFI]

Thank you very much, that was exactly what I needed. The only issue with it is that it doesn't know when it has printed all the permutations i.e. if there are only 8 it keeps on cycling those 8 lines as long as you keep on copying the formula down. Not a big issue since you can check the cumulative total from C2 and just take that many lines.

 

[shg]

You're welcome, glad it helped.