Generating full list of scenarios (or combinations) of data in columns

Hello,

I have a number of scenarios to test for and I am trying to pull a total number together (plus actually have a list of all the possible combinations) of data or options that are available in columns in a table.

As an example:

Column A contains a list:
Item A
Item B
Item C

Column B contains a different list:
Item D
Item E

Column C contains a different list:
Item F

All of the items can crop up in a scenario, and there are multiple combinations. For example:

Scenario 1: Item A, Item D, Item F
Scenario 2: Item A, Item E, Item F
Scenario 3: Item B, Item D, Item F
Scenario 4: Item B, Item, E, Item F
Scenario 5: Item C, Item D, Item F
Scenario 6: Item C, Item E, Item F

I have manually had to work out all of the combinations - I'd like Excel to do that for me. In reality I have about 10 columns and some lists contain about 10 different data items.

Any ideas on how I can achieve this would be amazing - given that I believe there are about 5000 possible unique combinations/scenarios!


Thanks,

Chris

I could give you a way of doing this for the 3 columns in your example, but it might not be obvious to you how to extend this to your real situation of 10 columns. Post an example file showing the 10 columns with their data items.

To attach a file, click on Go Advanced while you are posting a reply, then scroll down and click on Manage Attachments, then follow the on-screen prompts (basically, navigate to the file and select it, Upload it, Close the window, then Submit post).

Pete

Hi Pete - thanks for your response. I have had to change some of the data as it is sensitive, but this is the format I'm working in with the right number of options per column.

As you have a variable number of entries in each column, it would help to put this formula in B2:

=COUNTA(B3:B8)

then copy across to M2.

With these types of problems, I like to start with the right-most column, so put this formula in M14:

=INDEX(M$3:M$4,MOD(ROWS($1:1)-1,M$2)+1)

and copy it down a few rows - you can see that the two entries alternate. Then you can put this formula in L14:

=INDEX(L$3:L$5,MOD(INT((ROWS($1:1)-1)/PRODUCT(M$2:$M$2)),L$2)+1)

and copy that down a few rows - hopefully you can see the pattern emerging. Strictly speaking, you don't need the PRODUCT(M$2:$M$2) term here, as it could be replaced by just M$2, but that formula can now be used in all the other columns. Copy it into K14 and you will get this:

=INDEX(K$3:K$5,MOD(INT((ROWS($1:1)-1)/PRODUCT(L$2:$M$2)),K$2)+1)

and when you copy that down a few rows you will see the pattern more strongly. Just copy the formula from K14 into B14:J14, and then copy the formulae down the rows. Put this formula in A2 (widen the column to see the result):

=PRODUCT(B2:M2)

to tell you how many rows you will need. Add 14 onto it (as you started in row 14) to tell you where the pattern will start to repeat.

Hope this helps.

Pete

This is exactly what I needed - you have helped so much!

Thanks Pete!


Chris

Glad to hear that, Chris - thanks for feeding back.

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