MrExcel.com - Your One Stop for Excel Tips & Solutions

Dealing with a Puzzle

May 02, 2013 - by

Kris originally wrote looking for a 26x26x26 grid of letters A-Z where the rows, columns, and Z-axis never contain a repetitive letter.

On Monday, April 29 2013, the podcast created random letters between A & Z. Podcast 1698:

Video Transcript

MrExcel podcast is sponsored by "Easy-XL"!

Learn Excel from MrExcel podcast, episode 1698 - Generate Random Letters!

Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. Today's question from Kris, Kris wants to generate a 26x26 grid of random letters, letters between A and Z. So here's where we start: =CODE, the CODE of the letter A tells us the ASCII code of the letter A, it's 65. And then the same thing, CODE of the letter Z is 90, 65-90. So our goal is to generate random letters, characters between ASCII code 65 and ASCII code 90. So I'm going to choose my whole big range of 26x26 here, and then =C-H-A-R! What character we want? We want 65 all the way up through 90. So we're going to use another function, RANDBETWEEN! RANDBETWEEN will give us the random number between 65 and 90, )) . There we go, I'm going to press Ctrl+Enter here to enter that same formula in all cells of the selection. And we now have a 26x26 grid of random letters. Every time we press the F9 key, we will get a new set of letters. When we finally find a set that we like, we want to keep those, then of course Ctrl+C to copy, and then the right-click key, that's on the right hand side, between Alt and Ctrl, and V to convert those formulas to values. There you go, 26x26 grid.

Alright well hey, I want to thank Kris for sending that question in, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!

On Tuesday, April 30, 2013, the podcast dealt with how to build a random row of 26 letters without any repeats. Podcast 1699:

On Wednesday, May 1, 2013, the podcast talked about the math behind a 26x26 grid and that the math seems to indicate it is not possible to build such a grid. But, yet, somehow, there must be a math error because I have such a grid, even if it is not very random. Podcast 1700:

I later figured out some of the math error that I made in this episode. I had assumed that putting a "B" in column 2 of the grid would eliminate 1/26 of the remaining possible rows, assuming a random distribution of letters in the rows. However, once you've eliminated all the rows that have an "A" in column 1, it forces columns 2-26 to have a disproportionately larger number of A's than the other letters. Thus, when you remove all rows that have a B in column 2, you are not removing 1/26th of them. You are removing slightly less than 1/26 of them. For example, I had used 0.038342, but the actual number was 0.038247. This is the smallest error I've ever made in my life, but when you are multiplying by 403 septillion, it makes a difference. Using the macro from tomorrow's episode, I now believe that I can generate 6.255 sexdecillion 26x26x26 grids that have no repeats. That is 6.255E+51 or 6,255,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000. In Excel, use =FACT(26)*FACT(25).

Here is a great visual to explain the above. In the first figure, I've randomly selected 900,000 random rows out of the 407 septillion possibilities. Each letter is more or less randomly distributed throughout each column:

Figure 1
Figure 1

Say that your row 1 has a B in column 1. Filter out all of the rows that start with B. Since every row has to have a B, that makes the remaining 25 columns have more B's than anything else. You can see that clearly here:

Figure 2
Figure 2

If the letters in column B were equally distributed, you would expect 3.8461538% of each letter. However, there are 4.0153581% of B and an average of 3.8393857% of each other letter. Again, comparing 3.8461538% to 3.8393857% seems like a small error, but when multiplied by 407 septillion, it produces an error of 27 sextillion rows that podcast 1700 threw away in error.

On Thursday, May 2, 2013, a macro that successfully builds a grid of 26x26 without repeats. Podcast 1701:

Several viewers asked for the macro used in episode 1701. Right-click and choose Save Target As: Podcast1701.zip

Using an idea posted on Wednesday by YouTube viewer TomSaladin, here is a workbook with 26x26x26 with no repeats. Right-click and choose Save Target As: Grid26.zip