Excel All Combinations One To Six in Four Columns - Episode 2604

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on May 25, 2023.
Microsoft Excel Tutorial: Generating all combinations of N outcomes for K games.

In episode 2602, I had to generate all possible combinations of 4 games with 6 possible outcomes in each game. I used my very convoluted "binary count up, but not binary because it is base 6, but not base 6 because I need the digits 1 to 6 instead of 0 to 5" method. This involves a lot of typing and two different formulas.

Today, a much easier way from Kyle Freistedt. Kyle DOES use Base 6. He does use a SEQUENCE function but starts at 0 instead of 1. And then at the end, he uses +1111 to convert the 0 to 5 to 1 to 6.

In this video, I show Kyle's method for the Jeopardy Masters problem and then I generalize the steps for any values of N and K.

To download this workbook: Excel All Combinations One To Six in Four Columns - Episode 2604 Sample Files - MrExcel Publishing

Table of Contents
(0:00) Problem Statement
(0:24) Bill's Convoluted Method
(0:43) Kyle's Formula for Combinations using BASE
(1:00) Start the SEQUENCE at 0
(1:12) BASE function in Excel
(1:32) Clever: Add 1111
(2:02) One formula
(2:13) General steps for any N and K
(3:30) Break with MID and SEQUENCE?
(4:00) Break with TEXT and TEXTSPLIT?
(4:55) Thanks to Kyle
maxresdefault.jpg


Transcript of the video:
Hey, welcome back to MrExcel NetCast. I'm Bill Jelen.
A couple days ago I was solving the “Jeopardy Masters - What are the odds of a tie?”.
And I had to do all combinations.
That did just one to six, six different scenarios for four different games.
And I did this horrible formula for counting up in binary, but it wasn't binary.
It was sort of Base 6, but not really Base 6 because it went from one to six instead of zero to five.
Yeah, I use this method a lot. I'm not proud of this method.
Just type the numbers one to six here, and then grab the number from six rows above, copy that down.
And then the formula here, checking to see if the number to the right of us is a one. The number above us is a six.
If it is, then add one to the number above us, unless it's already a six, then reset back to one.
And thankfully, Kyle Freistedt set me again the easy steps to do this.
He did this the last time when I was doing the World Cup.
And honestly, yesterday I knew there was an easier way to do it.
I just couldn't remember what it was. I'm going to document it and try and learn it.
The first thing we do is we're going to do 1,296 numbers, but not starting from one.
We're going to start from zero like that.
We get the number zero to 1,295. That's our 1,296 numbers.
And then Kyle uses equal BASE, and we're going to convert that to base six.
Oh-Six-Hash we'll get all 1,296 numbers in Base 6 with a minimum length of four.
All right. And that generates almost what I need.
All right.
But what I need is instead of 0, 0, 0, 0, I need 1, 1, 1, 1.
Very cleverly, Kyle just adds, it's amazing that this works, +1111, like that. And we get exactly what I need.
Now it's all in one column, so I'm going to do some tricks to break it out into four columns.
But this is much easier than what I had done here with two different formulas and just a bunch of typing. It's one of those things I just have to learn.
And then of course, Kyle put it all in one formula like that.
We don't need the extra columns. It's just that formula.
For me, it's easier to explain it in the multiple columns.
All right. Here's the big test.
Let's do some other scenario.
Let's say that we have, I don't know, three columns and the number of scenarios is seven. All right.
The first thing to do is figure out how many rows we're going to need.
That's the number seven, raised to the third power, because for the first column, it could be the number one to seven, so there's seven possibilities.
And then for the second column, again, the numbers, so seven times seven times seven is seven raised to the third power. But I'm just trying to make this nice and general.
All right.
We know that we have 343, and so we're going to do a sequence of that number starting from zero.
We get the number zero to 342. All right.
And then, we're going to wrap that whole thing in the base function.
And for the radix, it is this number, this input cell here, seven.
And the minimum number of digits is the number of columns that we want at the end, so three.
Cool. Great.
And then from there, this great trick from Kyle, just add in this case +111 because it's only three digits.
Beautiful.
Now I'm starting to think about how I'm going to break that back out. It's funny.
TEXTSPLIT can break at a dash, but it can't say break it into every one character.
I'm thinking ahead here on the fly. Could we do equal MID of this.
Where we're going to start? We're going to start at the SEQUENCE of three.
One row, three columns for a length of one.
Yeah, that would work.
And then double click to copy that down, except double click doesn't work for these array formulas.
Okay. Let me go back to the original plan here.
Sorry, this is longer.
I'm going to wrap the whole thing in the TEXT function, and I'm going to add those hyphens in. 0-0-0, like that.
And then here, I should be able to do equal TEXTSPLIT of this text, split it at the dash.
Good.
And then can I do AB9#, or is that going to run into the array of array problems? Of course, it's the array of array problems.
All right. We have our formula there.
At this point, I guess I'm going to go back to the old formula.
Come down to the bottom. That was control-down arrow.
Move over to the right with the right arrow.
Control-shift up, and then Control-D is the fast way to copy that down.
And then of course, copy and paste as values and go on with the rest of yesterday's video. Thanks to Kyle for sending this in.
He sent it in last time when I did the World Cup, and I just didn't...
It's one of those things I saw it. I was like, "Oh, that's cool.
But then the next time I needed it, I could not remember it.
Me personally, I'll have to bookmark this video.
So the next time it comes up, I can come back and use this great little formula here.
Instead of the just convoluted way that I did it yesterday.
All right.
Hey, thanks to Kyle for sending that formula in a second time. Thanks to you for stopping by.
We'll see you next time for another NetCast for MrExcel.
If you like these videos, please down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
 
Hello @MrExcel!

Another great video. While watching I noticed you were as let down as most of us probably are at the inability of TEXTSPLIT to handle a range of cells.

So, I wrote a recursive LAMBDA function that does just that. Hope you like it.

EXCEL
ABCDE
11-1-1111
21-1-2112
31-1-3113
41-1-4114
51-1-5115
61-1-6116
71-2-1121
Sheet6
Cell Formulas
RangeFormula
C1:E7C1=MXL(A1:A7,"-")
Dynamic array formulas.


Excel Formula:
=LAMBDA(arr,delim,[res],
    IF(ROWS(arr)=1,
        VSTACK(DROP(res,1),TEXTSPLIT(arr,delim)),
        LET(
            a,IF(res=0,"",res),
            MXL(DROP(arr,1),delim,VSTACK(a,TEXTSPLIT(TAKE(arr,1),delim)))
        )
    )
)
 
That's a great formula! Thanks for posting it.
 

Forum statistics

Threads
1,216,505
Messages
6,131,026
Members
449,616
Latest member
PsychoCube

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