Generate 3 sets of the numbers 1-20 with no repeats in any set.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1806: 3 Sets of Random No Repeats.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question sent in via YouTube from gamezilla.
Trying to generate a set of numbers (1-20 3 times) without any repeats within the 3 sets so you know 1 to 20 with no repeats and I want a whole number, not 0.95681 or anything like that.
All right simple enough.
Well, we're going to start off with strange numbers like that.
We're going to have chosen 20 sets here do =rand() and open parenthesis close parenthesis and enter.
Now he wants 3 sets he or she I don't know.
So, copy that across and we want 3 sets of these numbers like that.
And then there are going to be the final result set 1 set 2 set 3.
Where I say =rank (A2,A$2:A$21)equal rank of this number over here in a 2 comma within this whole range, number so I'm going to press F4 twice, once to lock everything down the second time to lock down just the Rows the way that formula is allowed to move to column B and column C as we copy to the right.
Alright, so, we will copy that formula throughout here and what we're going to get is each column is the numbers 1 through 20 with absolutely no repeats.
Now if you did this million times, it's possible that you out here to 15 digits of precision we're going to get the same number, but really, really, really unlikely, so at least I would say you could press F9 for the next 15 years and you're never going to see any repeats on those columns.
But, every once in a while you might.
All right.
Hey, 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 1806: 3 Sets of Random No Repeats.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question sent in via YouTube from gamezilla.
Trying to generate a set of numbers (1-20 3 times) without any repeats within the 3 sets so you know 1 to 20 with no repeats and I want a whole number, not 0.95681 or anything like that.
All right simple enough.
Well, we're going to start off with strange numbers like that.
We're going to have chosen 20 sets here do =rand() and open parenthesis close parenthesis and enter.
Now he wants 3 sets he or she I don't know.
So, copy that across and we want 3 sets of these numbers like that.
And then there are going to be the final result set 1 set 2 set 3.
Where I say =rank (A2,A$2:A$21)equal rank of this number over here in a 2 comma within this whole range, number so I'm going to press F4 twice, once to lock everything down the second time to lock down just the Rows the way that formula is allowed to move to column B and column C as we copy to the right.
Alright, so, we will copy that formula throughout here and what we're going to get is each column is the numbers 1 through 20 with absolutely no repeats.
Now if you did this million times, it's possible that you out here to 15 digits of precision we're going to get the same number, but really, really, really unlikely, so at least I would say you could press F9 for the next 15 years and you're never going to see any repeats on those columns.
But, every once in a while you might.
All right.
Hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
