How to Generate Possible outcomes

[H.Merlow]

Hi guys,

I need help on how to generate all possible outcomes from a set of data

for example, if a value was to be picked from each column, how do I get all possible combinations?

column 1 column 2 column 3
A 1 e
B 2 f
C 3 g
D 4 h

Sorry I tried to add an excel attachment but I was unable to, that's the only way i could put up an example

Assistance will be appreciated, thank you.

[mikeTRON]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[newdoverman]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Click on Browse under Upload Files from your Computer and then find the file on your computer using the dialogue that opens. Click on Open then click on Upload. Click on Close this Window. Click on Submit

[H.Merlow]

I have attached the before sheet and the after sheet with the sample results. My question is on how to get all such combinations from the data

[newdoverman]

Here is one possible way to create a listing of all the combinations that you want. I am assuming that you want to keep the column order in order. There are too many equations to list and make sense. In the enclosed workbook, the yellow area is the starting equation and the green areas are where the formula changes.
E1 value is the count of column B * the count of column B. E2 is the count of column B.

This combines all the values in column A with all the values in columns B and C with all values for A calculated first then all values for B etc.

[H.Merlow]

This helps a lot, thank you.

[newdoverman]

Thank you for the feedback.

When you are satisfied that you have received the solution you want, use the tools at the top of the message listing and mark the thread as SOLVED.

[Pete_UK]

If you want a single formula, then you can use this (e.g. in cell E1):

=IF(ROWS($1:1)>6*6*6,"",INDEX($A$1:$A$6,MOD(INT((ROWS($1:1)-1)/36),6)+1) & INDEX($B$1:$B$6,MOD(INT((ROWS($1:1)-1)/6),6)+1) & INDEX($C$1:$C$6,MOD(ROWS($1:1)-1,6)+1))

then copy down until you start to get blanks.

Hope this helps.

Pete

[H.Merlow]

Before I mark as solved, what if I need my results to only have combinations where only one item is picked from each row as shown in the sample result

[newdoverman]

I don't understand your added requirement or the example.

A minor correction to the formula in the worksheet that I uploaded. Enter in G2 and fill down

Please Login or Register  to view this content.

@ Pete
Nifty!

[Pete_UK]

Hi Ron,

thanks for the rep.

I've done quite a few of these in the past, and the MOD and INT functions with INDEX are by far the easiest ways. I usually put each in their own columns and then concatenate them as you did (and , indeed, that it what I did here first of all) but then I thought that as there are only 3 columns then I could combine them into one formula.

Pete

[newdoverman]

Hi Pete. I have only done a couple of these and every time, I'm "re-inventing the wheel" Your methodology makes perfect sense and is quite quickly calculated.
Well done.

[Pete_UK]

Before I mark as solved ...

I think what you are asking for in your follow up question is quite a big deviation from your original request. You seem to want to take 2 distinct items from each column in any order, although it is difficult to generalise from only two examples.

You need to explain your requirements more fully, with further examples, and it might be better to finish with this thread (as you have had some detailed help which solves the original request) and start a new thread, with a link back to this one if you think it is relevant.

To re-iterate what Ron has said:

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[H.Merlow]

I actually want to take one distinct item from each row to have as many unique combinations as possible as shown in the mock results part.

[Pete_UK]

Your desired result shows:

A2jD5m

and:

AiC4l6

and to me that looks like two items from column A (shown red), 2 items from column B (shown black), and 2 items from column C (blue), none of which are repeated (i.e. distinct items), and they are in different positions in the 6-character string.

Perhaps you can explain your logic more clearly (hopefully in another thread now that you have marked this thread as solved).

Pete

[Pete_UK]

I see you haven't started a new thread, but I think I now know what you want to achieve. Put this formula in G1:

=IF(ROWS($1:1)>POWER(3,6),"",INDEX($A$1:$C$1,MOD(INT((ROWS($1:1)-1)/(9*27)),3)+1) & INDEX($A$2:$C$2,MOD(INT((ROWS($1:1)-1)/(3*27)),3)+1) & INDEX($A$3:$C$3,MOD(INT((ROWS($1:1)-1)/27),3)+1) & INDEX($A$4:$C$4,MOD(INT((ROWS($1:1)-1)/9),3)+1) & INDEX($A$5:$C$5,MOD(INT((ROWS($1:1)-1)/3),3)+1) & INDEX($A$6:$C$6,MOD(ROWS($1:1)-1,3)+1))

Then copy down to row 729, as you have 3 ^ 6 (=729) combinations taking them row-wise.

Hope this helps.

Pete

EDIT: the two examples you gave occur on rows 141 and 179.