Forum

Notifications
Clear all

Generate all possible combinations

3 Posts
2 Users
0 Reactions
224 Views
(@excelace)
Posts: 2
New Member
Topic starter
 

Hi All Excel Ninjas,

This is my 1st post here, below here is my problem.

I have data in two columns. Column A1 - A6 contains : AA, BB, CC, DD, EE, FF. And column B1-B3 contains +1, -1, 0

In column C and D, I want to populate the following - 

1st scenario:

Col C    Col D

AA       +1

BB       +1

CC       +1

DD      +1

EE       +1

FF       +1

2nd scenario:

Col C    Col D

AA       -1

BB       +1

CC       +1

DD      +1

EE       +1

FF       +1

3rd scenario:

Col C    Col D

AA       0

BB       +1

CC       +1

DD      +1

EE       +1

FF       +1

4th scenario:

Col C    Col D

AA       +1

BB       -1

CC       +1

DD      +1

EE       +1

FF       +1

5th scenario:

Col C    Col D

AA       +1

BB       -0

CC       +1

DD      +1

EE       +1

FF       +1

so on and so forth

So essentially, I want to populate all possible combinations where AA - FF remains constant while +1,-1 and 0 changes for those 6 elements (AA - FF) in different combinations.

Hope my query is clear. Looking forward to your help.

Regards,

D

 
Posted : 06/05/2022 1:48 pm
(@questvba)
Posts: 125
Estimable Member
 

Hi Gogol D,

I suggest a solution with Power Query and structured tables. This way, if you add or delete data, Excel calculates for you.

BR,

Lionel

 
Posted : 07/05/2022 1:55 am
(@excelace)
Posts: 2
New Member
Topic starter
 

Thanks for sharing this Lionel.

If you see, the proposed solution populates 6X3 = 18 combinations / scenarios. However, if you observe, the number of possible scenarios seem to be even pretty higher. What I mean by is that, now there will be possible permutation and combinations within these 18 observations as well and I need to populate the same.

Any further leads?

Best
G

 
Posted : 07/05/2022 8:23 am
Share: