Yesterday, in Episode #1698, Bill looked at Generating Letters Randomly. Today, in Episode #1699, Bill will look at how to Generate Letters in Excel Randomly with No Repeats, Following along with MrExcel learn how to add a magic touch to the work in order to generate a 26 x 26 grid of Random Letters with No Repeats in each of the Rows.
And for more information on Excel 2013, check out "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!
And for more information on Excel 2013, check out "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 1699 - Random, but No Repeats!
Well hey yesterday, Kris asked about generating at 26x26 grid where it was filled with letters from A-Z randomly, but now today, we learned that Kris is actually trying to make sure that there's no repeats in each row.
So every row contains all of the letters from A-Z, but we don't want it repeats.
So we're going to ask for the CHAR ROW of A65, that's asking for character 65, and I'll copy that down 26, that should get us the letters A-Z, right?
And then right here, =RAND, that just gives us a random decimal from 0-1, 15 digits of precision.
So in theory, there's going be no duplicates here, certainly not in this small population of 26.
So let's sort column A, we want the =SMALL of all of those values over there, F4 to lock that down, and then ROW of A1, that's a geeky way of writing the #1.
The beautiful thing about that is, as we copy it down, it'll give us the 1st smallest, the 2nd smallest, 3rd smallest, 4th smallest, and so on.
OK, now let's get the letters over here, so we're going to use =INDEX of those letters, F4, and we want a number from 1-26.
And so, what we're going to ask for is the MATCH, the MATCH of this number in this range over here, press F4, ,0 .
Alright, you notice that every time I enter a new formula everything changes, but that's ok, because now we're getting the complete set of 26 letters.
Alright, now let's twist those letters, so =INDEX of this range, F$.
Now here I want the first one, so I'm going to ask for the column of A1, and as I copy that across, we should get this set of data twisted sideways, transposed.
But it's transposing a formula, not just doing a single transpose.
Now at this point, we can probably create a data table, so, want the numbers 1-26 down here because we want 26 answers.
Select this whole range, and Data, What-If Analysis, Data Table.
The Row input cell we're going to leave blank, column input cell, we're going to take each one of these numbers one at a time and just put them, I don't care where, it doesn't matter, just any random cell, click OK.
And what we should end up with there is a 26x26 grid, random letters, and each row has every letter exactly once.
A lot harder than what we did yesterday, yesterday we just generated random numbers, when you need random without repeats, it becomes much, much more difficult.
Now, thinking about this, did we even have to put these letters here A-Z, or could we have- Actually, once we have that MATCH, that MATCH is returning the numbers from 1-26, so for returning 1-26 we could ask for the =CHAR of 64+ the MATCH, and that would also randomly get us 26 letters.
And we don't have to put the letters over here at all, we just need the RAND function.
OK, you can see it, I don't know if you were able to see it, that little spinning circle, it's actually starting to take a little bit of time to recalculate this now, because it's, it has to basically sort with the formula 26 times.
A lot more complicated, to get the 26x26 grid, where each row contains every letter exactly once.
Alright well hey, thanks to 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!
Learn Excel from MrExcel podcast, episode 1699 - Random, but No Repeats!
Well hey yesterday, Kris asked about generating at 26x26 grid where it was filled with letters from A-Z randomly, but now today, we learned that Kris is actually trying to make sure that there's no repeats in each row.
So every row contains all of the letters from A-Z, but we don't want it repeats.
So we're going to ask for the CHAR ROW of A65, that's asking for character 65, and I'll copy that down 26, that should get us the letters A-Z, right?
And then right here, =RAND, that just gives us a random decimal from 0-1, 15 digits of precision.
So in theory, there's going be no duplicates here, certainly not in this small population of 26.
So let's sort column A, we want the =SMALL of all of those values over there, F4 to lock that down, and then ROW of A1, that's a geeky way of writing the #1.
The beautiful thing about that is, as we copy it down, it'll give us the 1st smallest, the 2nd smallest, 3rd smallest, 4th smallest, and so on.
OK, now let's get the letters over here, so we're going to use =INDEX of those letters, F4, and we want a number from 1-26.
And so, what we're going to ask for is the MATCH, the MATCH of this number in this range over here, press F4, ,0 .
Alright, you notice that every time I enter a new formula everything changes, but that's ok, because now we're getting the complete set of 26 letters.
Alright, now let's twist those letters, so =INDEX of this range, F$.
Now here I want the first one, so I'm going to ask for the column of A1, and as I copy that across, we should get this set of data twisted sideways, transposed.
But it's transposing a formula, not just doing a single transpose.
Now at this point, we can probably create a data table, so, want the numbers 1-26 down here because we want 26 answers.
Select this whole range, and Data, What-If Analysis, Data Table.
The Row input cell we're going to leave blank, column input cell, we're going to take each one of these numbers one at a time and just put them, I don't care where, it doesn't matter, just any random cell, click OK.
And what we should end up with there is a 26x26 grid, random letters, and each row has every letter exactly once.
A lot harder than what we did yesterday, yesterday we just generated random numbers, when you need random without repeats, it becomes much, much more difficult.
Now, thinking about this, did we even have to put these letters here A-Z, or could we have- Actually, once we have that MATCH, that MATCH is returning the numbers from 1-26, so for returning 1-26 we could ask for the =CHAR of 64+ the MATCH, and that would also randomly get us 26 letters.
And we don't have to put the letters over here at all, we just need the RAND function.
OK, you can see it, I don't know if you were able to see it, that little spinning circle, it's actually starting to take a little bit of time to recalculate this now, because it's, it has to basically sort with the formula 26 times.
A lot more complicated, to get the 26x26 grid, where each row contains every letter exactly once.
Alright well hey, thanks to 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!
