Is this possible? How hard would it be?

[BornInTheFlame]

Hi Gents,

I would like to ask a question on whether this is possible to do in Excel and also how hard it would be? A major thank you in advance to anyone who is willing to take the time to drop/share some knowledge here. For anyone who can help me with this (if it is possible). So, let me get on to the question:

I would like to create a sort of 'Combination Generator' in Excel. As shown in my screenshot below I have 5 columns, all having 5 values each. What I am seeking to do is to somehow look at the cells in these columns, and then somehow generate a list of all possible combinations (if that makes sense). As seen in "Output Example 1" and "Output Example 2" i have done two random combinations to kind of give you an idea of what im trying to do. I'm not sure what the math would be, but im sure there would end up being hundred's of different combinations generated.

Would anyone be so kind to let me know how difficult this would be to do and whether its even possible? Please also let me know if this is confusing/not clear? I tried my best but I can clarify further if needed.

Thank You

 

[jasonb75]

There is a similar question with several suggestions that you should be able to adapt to your data.

All combinations of multiple columns of values?

I have four columns of values. I am looking for a solution - be it formula, macro, or anything else! - to produce a list of all combinations of those four columns of values, separated by a character of my choosing. For example: A B C D 1 Horse Red 20" Web 2 Pig Blue 30" Retail I...

www.mrexcel.com

 

[BornInTheFlame]

There is a similar question with several suggestions that you should be able to adapt to your data.

All combinations of multiple columns of values?

I have four columns of values. I am looking for a solution - be it formula, macro, or anything else! - to produce a list of all combinations of those four columns of values, separated by a character of my choosing. For example: A B C D 1 Horse Red 20" Web 2 Pig Blue 30" Retail I...

www.mrexcel.com

Thanks Jason - looking into this thread now. Quick question - how do i calculate the amount of possible combinations (assuming all columns have an equal about of values). What would the math formula be?

 

[jasonb75]

I believe that it would be =5^5 or =Number of rows ^ Number of columns.

This is on the assumption that the columns remain in order, if you wanted combinations like "John Walks, Karen Walks" and "Karen Walks, John Walks" then it will be much higher.

 

[BornInTheFlame]

I believe that it would be =5^5 or =Number of rows ^ Number of columns.

This is on the assumption that the columns remain in order, if you wanted combinations like "John Walks, Karen Walks" and "Karen Walks, John Walks" then it will be much higher.

Thank you very much Jason, they would all remain in order. I will mark this thread as [SOLVED] and post the solution I used from the thread you linked once i get it working.

 

[BornInTheFlame]

Update: This thread has been solved using the following answer in thread (kudos to Jason)

Permutation in Excel

I have the following: (A,B,C,D) and (1,2,3) and (X,Y,Z) So I would like to get the following combinations A1X,A1Y,A1Z, A2X,A2Y,A2Z, ..., D3X,D3Y,D3Z How is this done in excel?

www.mrexcel.com