All possible combinations in 7 rows

[mtordin]

I have 7 rows, each row has the options "Low", "Medium", and "High". I need to combine all 7 rows so for example one possible combination would be "LLLLLLL", then "LLLLLLM", then "LLLLLLH", then "LLLLLML" and so on and so forth. The order does matter because each row represents something different. How could I create a formula to make this work? I have found a formula which combines everything in two rows but I am not an excel expert and I dont know how to expand it to 7 rows. The formula is:

=IF(ROW()-ROW($D$1)+1>COUNTA($A$1:$A$4)*COUNTA($B$1:$B$3),"",INDEX($A$1:$A$4,INT((ROW()-ROW($D$1))/COUNTA($B$1:$B$3)+1))&INDEX($B$1:$B$3,MOD(ROW()-ROW($D$1),COUNTA($B$1:$B$3))+1))

I dont know if this will help but I hope it does. Any help is appreciated thank you!

 

[Eric W]

Welcome to the forum.

Put this formula in row 1 of some column, then drag down.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(BASE(ROW()-1,3,7),"0","L"),"1","M"),"2","H")

 

[mtordin]

I tried it but I just get an error that says "#NAME?"

 

[Eric W]

BASE was added to Excel in 2013, so evidently your version is before that. You can try this more complicated version instead:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(RIGHT("000000"&MMULT(MOD(INT((ROW()-1)/{1,3,9,27,81,243,729}),3),{1;10;100;1000;10000;100000;1000000}),7),"0","L"),"1","M"),"2","H")

 

[shg]

Another way:

	A​	B​	C​	D​	E​	F​	G​	H​
2​	L	L	L	L	L	L	L	A2: =MID("LMH", MOD(INT(3 * (ROWS(A$2:$G2) - 1) / 3 ^ COLUMNS(A$2:$G2)), 3) + 1, 1)
3​	L	L	L	L	L	L	M
4​	L	L	L	L	L	L	H
5​	L	L	L	L	L	M	L
6​	L	L	L	L	L	M	M
7​	L	L	L	L	L	M	H
8​	L	L	L	L	L	H	L
9​	L	L	L	L	L	H	M
10​	L	L	L	L	L	H	H
11​	L	L	L	L	M	L	L

 

[mtordin]

Another way:

	A​	B​	C​	D​	E​	F​	G​	H​
2​	L	L	L	L	L	L	L	A2: =MID("LMH", MOD(INT(3 * (ROWS(A$2:$G2) - 1) / 3 ^ COLUMNS(A$2:$G2)), 3) + 1, 1)
3​	L	L	L	L	L	L	M
4​	L	L	L	L	L	L	H
5​	L	L	L	L	L	M	L
6​	L	L	L	L	L	M	M
7​	L	L	L	L	L	M	H
8​	L	L	L	L	L	H	L
9​	L	L	L	L	L	H	M
10​	L	L	L	L	L	H	H
11​	L	L	L	L	M	L	L

<tbody>
</tbody>

I plugged in exactly what you have and nothing happened

 

[mtordin]

BASE was added to Excel in 2013, so evidently your version is before that. You can try this more complicated version instead:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(RIGHT("000000"&MMULT(MOD(INT((ROW()-1)/{1,3,9,27,81,243,729}),3),{1;10;100;1000;10000;100000;1000000}),7),"0","L"),"1","M"),"2","H")

you are a GOD thank you so much

 

[shg]

I plugged in exactly what you have and nothing happened

Gosh, and it worked so well here ...

 

[mass junior high]

Please sir,in a football match there are three outcomes, a win, a draw or lose. Selecting three games gives 27 possible outcomes. I found this code on an excellent forum to list all possible 27 outcomes
Sub stuff()
Const v& = 3
Dim z, y() As String, q()
Dim a&, b&, c&, d&, p&, MaxRow&
MaxRow = 65536
z = Array("H", "D", "A")
u = UBound(z) + 1
Redim y(1 To u ^ v, 1 To v)
For a = 1 To v
For b = 1 To u ^ v Step u ^ a
For c = b To b + u ^ (a - 1) - 1
For d = 1 To u
y(c + u ^ (a - 1) * (d - 1), v - a + 1) = z(d - 1)
Next d, c, b, a
For a = 1 To u ^ v Step MaxRow
Redim q(1 To MaxRow, 1 To 1)
p = p + 1
For b = 1 To MaxRow
If a + b > u ^ v + 1 Then Exit For
q(b, 1) = y(a + b - 1, 1)
For c = 2 To v
q(b, 1) = q(b, 1) & y(a + b - 1, c)
Next c
Next b
Cells(p).Resize(MaxRow) = q
Next a
End Sub
Am trying to list the possible outcomes for 4 matches which is 64 outcomes but each time I change the const v&=4 it gives me a wrong answer
Further more I will like a code to list all possible outcomes for12 matches taking just two possible outcomes , a win and a draw.

 

[Joe4]

Regarding "mass junior high's post
Duplicate: https://www.mrexcel.com/forum/excel...matches-list-all-64-possible-predictions.html

Please do not post the same question multiple times. All clarifications, directly-related follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #12 here: http://www.mrexcel.com/forum/showthread.php?t=99490).

If you do not receive a response, you can "bump" it by replying to it again, though we advise you not to bump a thread more than once a day.