Jon wants to generate 6-letter sequences using only the letters B and P.
So, for example: BBBBBB, PPPPPP, BPBPPB, BBBPPP, and so on. There are 64 such combinations and Bill shows you one way to solve this use BASE and SUBSTITUTE.
Formulas used for one word:
The red box suggested a shorter formula of:
Formula for all 64 words, in sequence
Formula for those 64 words sorted randomly
If you have the number of letters in A3, then generate all words in sequence
Sort those with:
So, for example: BBBBBB, PPPPPP, BPBPPB, BBBPPP, and so on. There are 64 such combinations and Bill shows you one way to solve this use BASE and SUBSTITUTE.
Formulas used for one word:
Excel Formula:
=SUBSTITUTE(SUBSTITUTE(BASE(RANDBETWEEN(0,63),2,6),"0","B"),"1","P")
Excel Formula:
=CONCAT(CHAR(66+14*RANDARRAY(6,1,0,1,TRUE)))
Excel Formula:
=SUBSTITUTE(SUBSTITUTE(BASE(SEQUENCE(64,1,0),2,6),"0","B"),"1","P")
Excel Formula:
=SORTBY(SUBSTITUTE(SUBSTITUTE(BASE(SEQUENCE(64,1,0),2,6),"0","B"),"1","P"),RANDARRAY(64))
Excel Formula:
=SUBSTITUTE(SUBSTITUTE(BASE(SEQUENCE(2^$A$3,1,0),2,$A$3),"0","B"),"1","P")
Excel Formula:
=SORTBY(SUBSTITUTE(SUBSTITUTE(BASE(SEQUENCE(2^$A$3,1,0),2,$A$3),"0","B"),"1","P"),RANDARRAY(2^$A$3))
Transcript of the video:
Learn Excel from MrExcel Podcast episode 2448. How can we randomly generate 6 letters BPBPPB.
Hey, welcome back to MrExcel netcast.
Completely easier one today from John Doe on YouTube.
Randomize the letters B & P in the sequence of six.
So it could be six B, Six P, or any combination thereof.
I'm sure there's many different ways to do this, but as soon as I looked at this I said well that looks like binary to me.
Instead of zeros and ones. We have B-s and P-s, so that's how I approached it.
How many letters are in the sequence? We just use the REPT function.
Repeat the number one six times to get the 111111.
That in decimal is a great old function called BIN2DEC. That is 63.
So that means the numbers 0 through 63 will generate all combinations that we need.
I also could have done that with two to the 6th which gets me 64 and subtract 1.
So either one of those will work alright.
So to randomly generate some numbers I used, in this case, RANDBETWEEN. Later we will use RANDARRAY.
So a random number between zero and 63.
And I want to convert that decimal number that decimal 45 into a binary string.
And thanks to Mordecai up in Long Island who passed along this great trick. The BASE function.
So I want the number 45 represented in base two, which is binary.
And then forcing the number of digits to appear - that argument is a beautiful argument – to make sure that we get 6 digits. So I'm pointing back to the 6 in A3.
There all right. So now I have almost solved it right?
Although it doesn't look like what John Doe is looking for.
But you know, we're really close. I use the SUBSTITUTE function.
To change each 1 to a B.
And then from that I use the SUBSTITUTE function to change each zero to a P.
And now I'm generating the B-s and P-s.
Of course I don't want to have this whole range here, so just putting everything together. We start off with the RANDBETWEEN 0 and 63.
Run that into the BASE function to generate a 6 digit string in binary.
SUBSTITUTE each zero for a B. SUBSTITUTE each one for a P.
And this is now a formula that I can copy and generate a bunch of such letters.
So I will just paste.
And now every time that I press F9 I'm getting a new combination. Here's a couple of interesting alternatives.
What if I want to generate every combination of these letters?
So the SEQUENCE of 64 rows, one column starting with zero, that will generate the number zero to 63.
And run that whole dynamic array into the SUBSTITUTE, SUBSTITUTE, BASE. That gets them in order.
So think about 000000 through. 111111.
But if you wanted to take those 64 and randomly organize them. Send that whole thing into the SORTBY.
And sorting by a RANDARRAY 64. What if the six letters was just an example?
What if you wanted four or eight or something like that?
So it's easy to take this and kind of customize it a little bit so I can put how many numbers I want to see. Four, even eight, twelve.
Twenty - ah of course it doesn't work with twenty because we don't have that many, well, we have exactly that many rows in Excel.
And I have two headings up here, so I guess 19 is the largest we could do. Even that's going to be a big number.
But not that bad, alright? That's an amazing formula right there.
That one formula is generating half a million answers and then sorting them pretty quickly.
What do we do here?
The number of letters in the sequence is stored in A3 and I have put that two to the third there inside the SEQUENCE.
Also A3 as the number of digits to generate. And then out here.
There were three places I had put in the reference to A3.
Inside the SEQUENCE. Number of digits to generate.
And then out here the RANDARRAY with 2 raised to the A3rd power.
I developed some brand new courses that will be available on the Retrieve platform starting on December 20th. Also personalized video shoutouts.
Hey, 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 YouTube comments below.
I want to thank John Doe for sending that question in and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Let's hear you, Nancy!
Hey, welcome back to MrExcel netcast.
Completely easier one today from John Doe on YouTube.
Randomize the letters B & P in the sequence of six.
So it could be six B, Six P, or any combination thereof.
I'm sure there's many different ways to do this, but as soon as I looked at this I said well that looks like binary to me.
Instead of zeros and ones. We have B-s and P-s, so that's how I approached it.
How many letters are in the sequence? We just use the REPT function.
Repeat the number one six times to get the 111111.
That in decimal is a great old function called BIN2DEC. That is 63.
So that means the numbers 0 through 63 will generate all combinations that we need.
I also could have done that with two to the 6th which gets me 64 and subtract 1.
So either one of those will work alright.
So to randomly generate some numbers I used, in this case, RANDBETWEEN. Later we will use RANDARRAY.
So a random number between zero and 63.
And I want to convert that decimal number that decimal 45 into a binary string.
And thanks to Mordecai up in Long Island who passed along this great trick. The BASE function.
So I want the number 45 represented in base two, which is binary.
And then forcing the number of digits to appear - that argument is a beautiful argument – to make sure that we get 6 digits. So I'm pointing back to the 6 in A3.
There all right. So now I have almost solved it right?
Although it doesn't look like what John Doe is looking for.
But you know, we're really close. I use the SUBSTITUTE function.
To change each 1 to a B.
And then from that I use the SUBSTITUTE function to change each zero to a P.
And now I'm generating the B-s and P-s.
Of course I don't want to have this whole range here, so just putting everything together. We start off with the RANDBETWEEN 0 and 63.
Run that into the BASE function to generate a 6 digit string in binary.
SUBSTITUTE each zero for a B. SUBSTITUTE each one for a P.
And this is now a formula that I can copy and generate a bunch of such letters.
So I will just paste.
And now every time that I press F9 I'm getting a new combination. Here's a couple of interesting alternatives.
What if I want to generate every combination of these letters?
So the SEQUENCE of 64 rows, one column starting with zero, that will generate the number zero to 63.
And run that whole dynamic array into the SUBSTITUTE, SUBSTITUTE, BASE. That gets them in order.
So think about 000000 through. 111111.
But if you wanted to take those 64 and randomly organize them. Send that whole thing into the SORTBY.
And sorting by a RANDARRAY 64. What if the six letters was just an example?
What if you wanted four or eight or something like that?
So it's easy to take this and kind of customize it a little bit so I can put how many numbers I want to see. Four, even eight, twelve.
Twenty - ah of course it doesn't work with twenty because we don't have that many, well, we have exactly that many rows in Excel.
And I have two headings up here, so I guess 19 is the largest we could do. Even that's going to be a big number.
But not that bad, alright? That's an amazing formula right there.
That one formula is generating half a million answers and then sorting them pretty quickly.
What do we do here?
The number of letters in the sequence is stored in A3 and I have put that two to the third there inside the SEQUENCE.
Also A3 as the number of digits to generate. And then out here.
There were three places I had put in the reference to A3.
Inside the SEQUENCE. Number of digits to generate.
And then out here the RANDARRAY with 2 raised to the A3rd power.
I developed some brand new courses that will be available on the Retrieve platform starting on December 20th. Also personalized video shoutouts.
Hey, 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 YouTube comments below.
I want to thank John Doe for sending that question in and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Let's hear you, Nancy!
Last edited by a moderator:
