Bill is still working with our Randomly Generated Letter Grid today. The twist in today's Podcast is to produce a 26 x 26 Random Letter Grid with no Repeats in each Row and no Repeats in each column. Follow along with MrExcel, in Episode #1700, to see 'if' this can be done.
"Microsoft 2013 InDepth" -- by Bill Jelen. Excel 2013 In Depth is the beyond-the-basics, beneath-the-surface guide for everyone working with Excel 2013. Excel expert and MVP Bill Jelen provides specific, tested, proven solutions to the problems Excel users run into every day: the types of challenges other books ignore or oversimplify. Jelen thoroughly covers all facets of working with Excel 2013. Excel 2013 In Depth
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
"Microsoft 2013 InDepth" -- by Bill Jelen. Excel 2013 In Depth is the beyond-the-basics, beneath-the-surface guide for everyone working with Excel 2013. Excel expert and MVP Bill Jelen provides specific, tested, proven solutions to the problems Excel users run into every day: the types of challenges other books ignore or oversimplify. Jelen thoroughly covers all facets of working with Excel 2013. Excel 2013 In Depth
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
MrExcel podcast is sponsored by "Easy-XL"!
Learn Excel from MrExcel podcast, episode 1700 - 26 x 26 Letters With No Repeats Both Ways?
Alright well, it feels like this has been going on all week- Oh it has been going all week! Kris is actually looking for a 26x26 grid where there's no repeats going across the row, but no repeats going down the column.
Alright, so first thought "Let's just do brute-force and see if it works out." Alright, so here's our 26x26, we generated this yesterday using the random that repeats. And I added some conditional formatting in Alt O D formula that says "Hey, we're going to count everything above us." So B1-B1, this was actually written right up here, B1-B1, see if it's =B2, if that's >0 then we highlighted in yellow. So all of the duplicates are being highlighted in yellow, let's see how bad the situation is. Hey, I actually learned something from Mike Girvin's formulas! So we look at the MAX, this is a wrapper function. Now we're going to count everything from B1-B26, and see if it's equal to each of the 26 letters.
So the MID of that string, row 1-26, when we use Ctrl+Shift+Enter, that coerces that into array of 26 values, and the MID gladly returns all 26 values to the COUNTIF. COUNTIF returns 26 values, MAX counts them up, Ctrl+Shift+Enter!
And so here, there are the most, there's one letter at least that's repeated 3 times, so the S, the S, and the S right there, so we have S appearing three times. And what I thought I could do, is just kind of do a little MAX out here, keep pressing F9 repeatedly, until I finally get this MAX to be 0, then everything has just worked out. But before I sit here and start pressing F9 for the next 432 years, let's see if this is even possible. Alright, so let's do some basic statistics, how to build a row. First letter, we have 26 choices, and then the second letter, we have the remaining 25 choices, because we can't repeat in the row. And then 24, and 23, 22, all the way, so we have to multiply 26*25, and there is an Excel to do that and everything, it is called FACT! FACT which does a factorial back to 26. And that's scientific notation, let's convert it back to a regular old number with comments that we can count. Alright, wow, there's a lot. So there's millions, billions, trillions, quadrillions, quintillions, I don't even know. A big number of possibilities, this certainly is going to be possible, you would think!
Alright, here's my math of why I think this can't be done! A 26x26 grid has 676 cells.
As soon as I go to row 1 column 1 and I choose a letter, I don't care what letter, the letter F, I've just eliminated all of the rows that have an F in column 1. Alright, so there were 26 unique letters that were in column 1, I just eliminated the F, that's 1/26, that means I have 25/26 left. So, although I had 403 septillion before I started this, I'm now down to 387 septillion. And then, when I place the 2nd letter, I'm down to 372 septillion, third letter, and so on. By the time I get down to the bottom of the first row, alright I placed all 26 letters, I'm down from 403 septillion to 145 septillion. It still sounds like a really big number, right? But it keeps getting smaller and smaller and smaller, and according to my math, and I'm not sure my math is right, somewhere down around row 24, we run out. There's going to be no possible combinations left! It just isn't going to work. Here I have a chart here, let me scroll over. Alright, so we started with 403, that was before Row 1 you know, by the time I get down to row 10, I think we're down to 3.7 sextillion, and then down around row 20, we're down to only 32 trillion. It still sounds like a lot, but very quickly it just goes away, according to my math, there's no way this will ever work.
But hey, bad news, my math has to be wrong, because Kris sent me a 26x26 that was created manually. So something clearly is wrong here, there must be a solution to this. OK, so at the beginning of this podcast, I just tried to random 26x26 and that didn't work, let's see if we can get better than that. So here I have almost a million different unique combinations, not 407 septillion, because we only have a million rows in Excel. But I randomly selected one of these and said "Alright, that's going to be our row 1" and then I wrote a macro that went through and said "We're going to come back here, we're going to turn on the filter, Data, Filter, and for our first letter we're going to turn off the Z!" So open this filter drop down, turn off the Z, and click OK, and down at the bottom it'll tell us that you know we've lost some small percentage of the records. And then for column 2 I go and I turn off the filter for Y, and go all the way through. And after doing all of that we were down from 955.000 to 366.000, we lost about 64% of the possible rows. And then, of those that were left, out of these 366, I just randomly chose this, and now I had to go back through, and of course, at this point the macro is doing it. And in column 1 turning off the Z and the U, column 2 turning off the Y and the K, and we're down already very quickly to only 129.000 rows that'll still match, randomly chose one of those.
Change the filter, now to turn off all three letters in each column, 43696, and sure enough after row 11, I'm down to, that was it, there was only 5 left after row 10. I chose one of those 5, and I had wasted all my chances.
Now I only had a million rows in Excel, instead of 407 septillion rows in Excel, so I ran out a lot sooner than my math said I would have run out. But even if we go to PowerPivot, we're never going to get 407 septillion rows, this method just is not going to work. Now good news, there is a solution, tomorrow we'll show a completely different way to attack this problem.
Why hey, I want to thank Kris for sending this question in, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Learn Excel from MrExcel podcast, episode 1700 - 26 x 26 Letters With No Repeats Both Ways?
Alright well, it feels like this has been going on all week- Oh it has been going all week! Kris is actually looking for a 26x26 grid where there's no repeats going across the row, but no repeats going down the column.
Alright, so first thought "Let's just do brute-force and see if it works out." Alright, so here's our 26x26, we generated this yesterday using the random that repeats. And I added some conditional formatting in Alt O D formula that says "Hey, we're going to count everything above us." So B1-B1, this was actually written right up here, B1-B1, see if it's =B2, if that's >0 then we highlighted in yellow. So all of the duplicates are being highlighted in yellow, let's see how bad the situation is. Hey, I actually learned something from Mike Girvin's formulas! So we look at the MAX, this is a wrapper function. Now we're going to count everything from B1-B26, and see if it's equal to each of the 26 letters.
So the MID of that string, row 1-26, when we use Ctrl+Shift+Enter, that coerces that into array of 26 values, and the MID gladly returns all 26 values to the COUNTIF. COUNTIF returns 26 values, MAX counts them up, Ctrl+Shift+Enter!
And so here, there are the most, there's one letter at least that's repeated 3 times, so the S, the S, and the S right there, so we have S appearing three times. And what I thought I could do, is just kind of do a little MAX out here, keep pressing F9 repeatedly, until I finally get this MAX to be 0, then everything has just worked out. But before I sit here and start pressing F9 for the next 432 years, let's see if this is even possible. Alright, so let's do some basic statistics, how to build a row. First letter, we have 26 choices, and then the second letter, we have the remaining 25 choices, because we can't repeat in the row. And then 24, and 23, 22, all the way, so we have to multiply 26*25, and there is an Excel to do that and everything, it is called FACT! FACT which does a factorial back to 26. And that's scientific notation, let's convert it back to a regular old number with comments that we can count. Alright, wow, there's a lot. So there's millions, billions, trillions, quadrillions, quintillions, I don't even know. A big number of possibilities, this certainly is going to be possible, you would think!
Alright, here's my math of why I think this can't be done! A 26x26 grid has 676 cells.
As soon as I go to row 1 column 1 and I choose a letter, I don't care what letter, the letter F, I've just eliminated all of the rows that have an F in column 1. Alright, so there were 26 unique letters that were in column 1, I just eliminated the F, that's 1/26, that means I have 25/26 left. So, although I had 403 septillion before I started this, I'm now down to 387 septillion. And then, when I place the 2nd letter, I'm down to 372 septillion, third letter, and so on. By the time I get down to the bottom of the first row, alright I placed all 26 letters, I'm down from 403 septillion to 145 septillion. It still sounds like a really big number, right? But it keeps getting smaller and smaller and smaller, and according to my math, and I'm not sure my math is right, somewhere down around row 24, we run out. There's going to be no possible combinations left! It just isn't going to work. Here I have a chart here, let me scroll over. Alright, so we started with 403, that was before Row 1 you know, by the time I get down to row 10, I think we're down to 3.7 sextillion, and then down around row 20, we're down to only 32 trillion. It still sounds like a lot, but very quickly it just goes away, according to my math, there's no way this will ever work.
But hey, bad news, my math has to be wrong, because Kris sent me a 26x26 that was created manually. So something clearly is wrong here, there must be a solution to this. OK, so at the beginning of this podcast, I just tried to random 26x26 and that didn't work, let's see if we can get better than that. So here I have almost a million different unique combinations, not 407 septillion, because we only have a million rows in Excel. But I randomly selected one of these and said "Alright, that's going to be our row 1" and then I wrote a macro that went through and said "We're going to come back here, we're going to turn on the filter, Data, Filter, and for our first letter we're going to turn off the Z!" So open this filter drop down, turn off the Z, and click OK, and down at the bottom it'll tell us that you know we've lost some small percentage of the records. And then for column 2 I go and I turn off the filter for Y, and go all the way through. And after doing all of that we were down from 955.000 to 366.000, we lost about 64% of the possible rows. And then, of those that were left, out of these 366, I just randomly chose this, and now I had to go back through, and of course, at this point the macro is doing it. And in column 1 turning off the Z and the U, column 2 turning off the Y and the K, and we're down already very quickly to only 129.000 rows that'll still match, randomly chose one of those.
Change the filter, now to turn off all three letters in each column, 43696, and sure enough after row 11, I'm down to, that was it, there was only 5 left after row 10. I chose one of those 5, and I had wasted all my chances.
Now I only had a million rows in Excel, instead of 407 septillion rows in Excel, so I ran out a lot sooner than my math said I would have run out. But even if we go to PowerPivot, we're never going to get 407 septillion rows, this method just is not going to work. Now good news, there is a solution, tomorrow we'll show a completely different way to attack this problem.
Why hey, I want to thank Kris for sending this question in, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
