Generate all possible combinations

[Gogol D]

Dear All Friends,

This is my 1st post here and below is my problem statement.

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 create 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

 

[kweaver]

Not exactly what you asked for but generates all of the combos.

temporary.xlsm
	A	B	C
2	AA	+1	AA-+1
3	BB	-1	AA--1
4	CC	0	AA-0
5	DD		BB-+1
6	EE		BB--1
7	FF		BB-0
8			CC-+1
9			CC--1
10			CC-0
11			DD-+1
12			DD--1
13			DD-0
14			EE-+1
15			EE--1
16			EE-0
17			FF-+1
18			FF--1
19			FF-0
Sheet10

Cell Formulas
Range	Formula
C2:C19	C2	=IF(ROW()-ROW($D$2)+1>COUNTA($A$2:$A$7)*COUNTA($B$2:$B$4),"",INDEX($A$2:$A$7,INT((ROW()-ROW($D$2))/COUNTA($B$2:$B$4)+1))&"-"&INDEX($B$2:$B$4,MOD(ROW()-ROW($D$2),COUNTA($B$2:$B$4))+1))

 

[kweaver]

Another version (but still not exactly what you asked for) that maybe you can play with this:

=A2:A7&" "&INDEX(B2:B4,1+MOD(SEQUENCE(6,3)-1,3))

OR

=INDEX(A2:A7&" "&INDEX(B2:B4,1+MOD(SEQUENCE(6,3)-1,3)),ROUNDUP(SEQUENCE(18)/3,0),MOD(SEQUENCE(18,,0),3)+1)

 

[Gogol D]

Thanks for sharing this Kweaver.

As you have rightfully mentioned, this doesn't give what exactly I am looking for. If you see, all of these formulae are just populating 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

 

[kweaver]

You'll have to show me what other results you are expecting. With 6 and 3 there are only 18 as you pointed out.

 

[Gogol D]

You'll have to show me what other results you are expecting. With 6 and 3 there are only 18 as you pointed out.

Sure. I mean to say the following -

NBRX being +1, -1, or 0, there could be 10 scenarios while varying values for others. Likewise, many such scenario could be plotted. I just want to create those individual unique scenarios for further analysis. So I was thinking may be 5^3 such possibilities could be there. Please let me know your thoughts.

Scenario n1	NBRx +1
	TRx-1
	MS%-1
	Rejection-1
	Reversal-1
	purchase-1
Scenario n2	NBRx +1
	TRx0
	MS%0
	Rejection0
	Reversal0
	purchase0
Scenario n3	NBRx +1
	TRx+1
	MS%+1
	Rejection+1
	Reversal+1
	purchase+1
Scenario n4	NBRx0
	TRx0
	MS%0
	Rejection0
	Reversal0
	purchase0
Scenario n5	NBRx 0
	TRx-1
	MS%-1
	Rejection-1
	Reversal-1
	purchase-1
Scenario n6	NBRx 0
	TRx+1
	MS%+1
	Rejection+1
	Reversal+1
	purchase+1
Scenario n7	NBRx -1
	TRx-1
	MS%-1
	Rejection-1
	Reversal-1
	purchase-1
Scenario n8	NBRx -1
	TRx+1
	MS%+1
	Rejection+1
	Reversal+1
	purchase+1
Scenario n9	NBRx -1
	TRx0
	MS%0
	Rejection0
	Reversal0
	purchase0

 

[Gogol D]

Sorry

Sure. I mean to say the following -

NBRX being +1, -1, or 0, there could be 10 scenarios while varying values for others. Likewise, many such scenario could be plotted. I just want to create those individual unique scenarios for further analysis. So I was thinking may be 5^3 such possibilities could be there. Please let me know your thoughts.

Scenario n1	NBRx +1
	TRx-1
	MS%-1
	Rejection-1
	Reversal-1
	purchase-1
Scenario n2	NBRx +1
	TRx0
	MS%0
	Rejection0
	Reversal0
	purchase0
Scenario n3	NBRx +1
	TRx+1
	MS%+1
	Rejection+1
	Reversal+1
	purchase+1
Scenario n4	NBRx0
	TRx0
	MS%0
	Rejection0
	Reversal0
	purchase0
Scenario n5	NBRx 0
	TRx-1
	MS%-1
	Rejection-1
	Reversal-1
	purchase-1
Scenario n6	NBRx 0
	TRx+1
	MS%+1
	Rejection+1
	Reversal+1
	purchase+1
Scenario n7	NBRx -1
	TRx-1
	MS%-1
	Rejection-1
	Reversal-1
	purchase-1
Scenario n8	NBRx -1
	TRx+1
	MS%+1
	Rejection+1
	Reversal+1
	purchase+1
Scenario n9	NBRx -1
	TRx0
	MS%0
	Rejection0
	Reversal0
	purchase0

Sorry, I mean 6^3 such possibilities.

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: https://www.myonlinetraininghub.com/excel-forum/excel/generate-all-possible-combinations
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.