want to list/display all possible combinations

[jcbasch]

I'd like to display or list all possible combinations of data from 3 different columns. If for example there are 5 terms in column 1, 3 terms in column 2, and 2 terms in column 3, then I know the # of pos. combos , (5*3*2)=30. But how can I get excel to actually list and display all the combos?

 

[jcbasch]

thanks! I really appreciate all the help/info I received.

 

[rrdonutz]

Even though this topic seemingly has been exhausted, here's one more approach, albeit a non-VBA, non-custom function, non-Morefunc.xll one. Following the sample data pattern from Juan Pablo G. above, enter in the range A8:C8:

=COUNTA(A2:A6), COUNTA(B2:B6), COUNTA(C2:C6)

Then enter in cell A10:

=INDEX(A$2:A$6,MOD(INT(ROW()/(B$8*C$8)),A$8)+1)&","&INDEX(B$2:B$6,MOD(INT(ROW()/C$8),B$8)+1)&","&INDEX(C$2:C$6,MOD(ROW(),C$8)+1)

and copy down through A39 (range count = 30 cells = A8 * B8 * C8).

The array formula:

=SUM(1/COUNTIF(A10:A39,A10:A39))

returns 30, as it should (entered in cell A9, for example).

Note that as you change the number of sample items in cells A2:C6, the formula will return the correct number of combinations, provided that it's been copied far enough down the column. For a 5 x 5 x 5 sample, copy down through cell A134.

It's rather unsophisticated and unassuming in its approach, but it works.

--Tom

Note: I would enter the above using HtmlMaker 2.32, but my Windows NT4.0 / Excel 97 combination doesn't allow me to convert my spreadsheet. I get a "Could not load an object because it is not available on this machine" error message . . .

 

[Juan Pablo González]

Very nice Tom !

And that HTML Error that you're getting shouldn't prevent you from posting... read the FAQ of the HTML Maker to see that.