All possible combinations in 7 rows

M

mtordin

New Member
Joined
Mar 27, 2017
Messages
5
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!
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
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")
 
Upvote 0
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")
 
Upvote 0
Another way:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]
A​
[/td][td="bgcolor:#C0C0C0"]
B​
[/td][td="bgcolor:#C0C0C0"]
C​
[/td][td="bgcolor:#C0C0C0"]
D​
[/td][td="bgcolor:#C0C0C0"]
E​
[/td][td="bgcolor:#C0C0C0"]
F​
[/td][td="bgcolor:#C0C0C0"]
G​
[/td][td="bgcolor:#C0C0C0"]
H​
[/td][/tr][tr][td="bgcolor:#C0C0C0"]
2​
[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td]A2: =MID("LMH", MOD(INT(3 * (ROWS(A$2:$G2) - 1) / 3 ^ COLUMNS(A$2:$G2)), 3) + 1, 1)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
3​
[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]M[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
4​
[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]H[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
5​
[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]M[/td][td="bgcolor:#E5E5E5"]L[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
6​
[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]M[/td][td="bgcolor:#E5E5E5"]M[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
7​
[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]M[/td][td="bgcolor:#E5E5E5"]H[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
8​
[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]H[/td][td="bgcolor:#E5E5E5"]L[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
9​
[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]H[/td][td="bgcolor:#E5E5E5"]M[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
10​
[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]H[/td][td="bgcolor:#E5E5E5"]H[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
11​
[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]M[/td][td="bgcolor:#E5E5E5"]L[/td][td="bgcolor:#E5E5E5"]L[/td][td][/td][/tr]
[/table]
 
Last edited:
Upvote 0
shg said:
Another way:

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"]
A​
[/TD]
[TD="bgcolor: #C0C0C0"]
B​
[/TD]
[TD="bgcolor: #C0C0C0"]
C​
[/TD]
[TD="bgcolor: #C0C0C0"]
D​
[/TD]
[TD="bgcolor: #C0C0C0"]
E​
[/TD]
[TD="bgcolor: #C0C0C0"]
F​
[/TD]
[TD="bgcolor: #C0C0C0"]
G​
[/TD]
[TD="bgcolor: #C0C0C0"]
H​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]
2​
[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD]A2: =MID("LMH", MOD(INT(3 * (ROWS(A$2:$G2) - 1) / 3 ^ COLUMNS(A$2:$G2)), 3) + 1, 1)[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]
3​
[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]M[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]
4​
[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]H[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]
5​
[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]M[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]
6​
[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]M[/TD]
[TD="bgcolor: #E5E5E5"]M[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]
7​
[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]M[/TD]
[TD="bgcolor: #E5E5E5"]H[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]
8​
[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]H[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]
9​
[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]H[/TD]
[TD="bgcolor: #E5E5E5"]M[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]
10​
[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]H[/TD]
[TD="bgcolor: #E5E5E5"]H[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]
11​
[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]M[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD="bgcolor: #E5E5E5"]L[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

I plugged in exactly what you have and nothing happened
 
Upvote 0
Eric W said:
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")
Click to expand...


you are a GOD thank you so much
 
Upvote 0
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

serge
  • Question Question
#N/A cell skip.
Replies
3
Views
246
Peter_SSs
Peter_SSs
G
  • Solved
Formula to make combination of elements given possible variables?
Replies
5
Views
249
felixstraube
F
R
  • Question Question
Linked dynamic named ranges - unexpected results.
Replies
2
Views
99
Ramses505
R
R
  • Question Question
Replace cell reference
Replies
1
Views
275
LazyBug
LazyBug
C
  • Question Question
Combining rows 3 through 7 into row 2, one row takes a painfully long time with this code. Why?
Replies
2
Views
233
jolivanes
jolivanes

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top