Finishing off the week-long series, Bill looks at the probability that this grid may not become a reality. The Grid, a Random selection of 676 Letters placed into a 26 x 26 Grid, without Repeats in Each Row and without Repeats in Each Column. Follow along with Episode #1701 to find out if creating this Grid in Microsoft Excel is possible. The macro can be downloaded from Dealing with a Puzzle
"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 1701 - 26x26 Letters No Repeats Impossible?
Alright well, this is going to make sense if you've been watching all week, otherwise it's not.
Yesterday I'm trying to get a 26x26 grid of letters, where there's no repeats going across the rows, that was easy, we did that on Tuesday, but also no repeats going down the columns.
And I did some math yesterday that convinced me there's no way that this would happen, even if we had 407 septillion rows in Excel, we would still run out of room around letter 24(?).
Since we only have a million rows in Excel to list the possible alternatives, I only got through row 11.
So I said alright, let me just try and write a macro that will fill in the next row, right?
And so here's what we have: Here's the letters we've chosen so far, and we have a grid down here, how many times does this letter appear in this column.
Just a simple little COUNTIF, so we're counting, you know basically, it's either going to be 0 or 1, because we're not allowed to have any repeats, right?
And then I built this little grid, and this was actually used for yesterday's macro, if the letter's still valid for this column, put the letter, otherwise a number.
And I put a unique number, because when I used AutoFilter yesterday, I needed to be able to have that macro work, which is a long story that we don't need to get into today.
And then we're working on row 12, and of course, for the letter A there's 15 places where it can go, because we've already placed 11 letters.
So there's 15 places left which, you know, sounds really obvious.
But what's interesting is, once we choose a place for the letter A, well that knocks a few of these others down to 14.
Alright, and once we choose the place with the letter B, it might knock some of these down to 13, so what I realized is, we have to look for the ones that have the fewest number of choices and solve that one first.
Now the first pass-through, it's always going to be A that we're going to solve but, you know then, it might be more or something like that.
So let's come up here, and I'll run my macro, "Next Row", and BAM, it just filled it in!
Alright, so yesterday brute-force million rows and all these Auto filters, and the macro just works, Next Row.
A little bit slower that time, but it got it, and there it got it, and there it got it, oh no it didn't!
Alright, so in row 15 there was a blank there, and I was having it display a message saying "Hey, no luck, you're in trouble." But once I got that, I tried to run it again, and generally it works.
So filled in row 16, filled in row 17, filled in row 18, just looking for places to put the letters, attacking the letters that have the fewest possible locations first.
And I discovered, so like right here, there were two that didn't work, but that's okay, when we get one that doesn't work we just try again, and it works the second time, right?
So generally it works, and sure enough, yesterday I said "We would be out of possibilities by now." But clearly my math yesterday was wrong, because there, we just solved it!
So I saved this one, and said "I'm going to send this to Kris." And then, you know, I started all over, I ran it all again, and then I realized: Well wait a second, I don't need to be sitting here pressing Next Row Next Row, I can just have a macro that runs Next Row repeatedly.
And watch this, the whole thing is going to take about 30 seconds, which makes that whole process that I went through yesterday seem completely insane, because to do the Auto filters took me a few hours.
Initially doing things manually, and then realizing that was never going to work, and writing the macro to do it.
But here, you know, simply by just throwing this, you know, "Hey, let's look for a random place, and if it doesn't work well that's OK, we'll try that row over." It is consistently solving it over and over and over again.
Now I haven't checked to see if any of them are the same, like there's no way that any of these are the same.
I've generated, you know, 30 or 40 of them so far.
I'm guessing I could continue to do this all day, and generate new 26x26 grids that are completely unique.
So interesting question, when Kris sent this in, Kris actually started out saying "26x26x26!" Kris wanted 26 grids that never repeat, I said "Well there's absolutely no way that is ever going to happen!" But clearly it's possible to do the 26x26 with no repeats all the way through.
Let's just take a look at our formulas down here.
What we should see is all 1's, no 2's, no 0's, and you know, just working like a charm.
So there you go, a 26x26 grid, no repeats across the rows, no repeats down the columns.
Why hey, I want to thank everyone for sticking with it all week for the problem sent by Kris, finally have the (unclear), see you next time for another netcast from MrExcel!
Learn Excel from MrExcel podcast, episode 1701 - 26x26 Letters No Repeats Impossible?
Alright well, this is going to make sense if you've been watching all week, otherwise it's not.
Yesterday I'm trying to get a 26x26 grid of letters, where there's no repeats going across the rows, that was easy, we did that on Tuesday, but also no repeats going down the columns.
And I did some math yesterday that convinced me there's no way that this would happen, even if we had 407 septillion rows in Excel, we would still run out of room around letter 24(?).
Since we only have a million rows in Excel to list the possible alternatives, I only got through row 11.
So I said alright, let me just try and write a macro that will fill in the next row, right?
And so here's what we have: Here's the letters we've chosen so far, and we have a grid down here, how many times does this letter appear in this column.
Just a simple little COUNTIF, so we're counting, you know basically, it's either going to be 0 or 1, because we're not allowed to have any repeats, right?
And then I built this little grid, and this was actually used for yesterday's macro, if the letter's still valid for this column, put the letter, otherwise a number.
And I put a unique number, because when I used AutoFilter yesterday, I needed to be able to have that macro work, which is a long story that we don't need to get into today.
And then we're working on row 12, and of course, for the letter A there's 15 places where it can go, because we've already placed 11 letters.
So there's 15 places left which, you know, sounds really obvious.
But what's interesting is, once we choose a place for the letter A, well that knocks a few of these others down to 14.
Alright, and once we choose the place with the letter B, it might knock some of these down to 13, so what I realized is, we have to look for the ones that have the fewest number of choices and solve that one first.
Now the first pass-through, it's always going to be A that we're going to solve but, you know then, it might be more or something like that.
So let's come up here, and I'll run my macro, "Next Row", and BAM, it just filled it in!
Alright, so yesterday brute-force million rows and all these Auto filters, and the macro just works, Next Row.
A little bit slower that time, but it got it, and there it got it, and there it got it, oh no it didn't!
Alright, so in row 15 there was a blank there, and I was having it display a message saying "Hey, no luck, you're in trouble." But once I got that, I tried to run it again, and generally it works.
So filled in row 16, filled in row 17, filled in row 18, just looking for places to put the letters, attacking the letters that have the fewest possible locations first.
And I discovered, so like right here, there were two that didn't work, but that's okay, when we get one that doesn't work we just try again, and it works the second time, right?
So generally it works, and sure enough, yesterday I said "We would be out of possibilities by now." But clearly my math yesterday was wrong, because there, we just solved it!
So I saved this one, and said "I'm going to send this to Kris." And then, you know, I started all over, I ran it all again, and then I realized: Well wait a second, I don't need to be sitting here pressing Next Row Next Row, I can just have a macro that runs Next Row repeatedly.
And watch this, the whole thing is going to take about 30 seconds, which makes that whole process that I went through yesterday seem completely insane, because to do the Auto filters took me a few hours.
Initially doing things manually, and then realizing that was never going to work, and writing the macro to do it.
But here, you know, simply by just throwing this, you know, "Hey, let's look for a random place, and if it doesn't work well that's OK, we'll try that row over." It is consistently solving it over and over and over again.
Now I haven't checked to see if any of them are the same, like there's no way that any of these are the same.
I've generated, you know, 30 or 40 of them so far.
I'm guessing I could continue to do this all day, and generate new 26x26 grids that are completely unique.
So interesting question, when Kris sent this in, Kris actually started out saying "26x26x26!" Kris wanted 26 grids that never repeat, I said "Well there's absolutely no way that is ever going to happen!" But clearly it's possible to do the 26x26 with no repeats all the way through.
Let's just take a look at our formulas down here.
What we should see is all 1's, no 2's, no 0's, and you know, just working like a charm.
So there you go, a 26x26 grid, no repeats across the rows, no repeats down the columns.
Why hey, I want to thank everyone for sticking with it all week for the problem sent by Kris, finally have the (unclear), see you next time for another netcast from MrExcel!
