How to generate a random selection in Excel with no repeats.
Choose random names from the Data Set, but do it without repeating a namek. In a revisit to Episode #1471, how will Bill assure that there are no repeats in the Random selection? Follow along with Episode #2570 today to learn the answer and solve the challenge.
This video requires new Excel functions available in Microsoft 365. If you don't yet have RANDARRAY, SORTBY, or SEQUENCE, then use the old method in video 1471:
The PowerBall lotto formula shown near the end of the video is:
=TEXTJOIN(", ",,SORT(TAKE(
SORTBY(SEQUENCE(69),RANDARRAY(69)),5)),"PB="&RANDBETWEEN(1,26))
Table of Contents
(0:00) Choose 8 names from a list of 26 names
(1:18) Choose N names from a list of NN names
(2:55) Choose 5 numbers from 1 to 69 with another from 1 to 26
(3:35) Excel formula to generate many PowerBall selections
Choose random names from the Data Set, but do it without repeating a namek. In a revisit to Episode #1471, how will Bill assure that there are no repeats in the Random selection? Follow along with Episode #2570 today to learn the answer and solve the challenge.
This video requires new Excel functions available in Microsoft 365. If you don't yet have RANDARRAY, SORTBY, or SEQUENCE, then use the old method in video 1471:
The PowerBall lotto formula shown near the end of the video is:
=TEXTJOIN(", ",,SORT(TAKE(
SORTBY(SEQUENCE(69),RANDARRAY(69)),5)),"PB="&RANDBETWEEN(1,26))
Table of Contents
(0:00) Choose 8 names from a list of 26 names
(1:18) Choose N names from a list of NN names
(2:55) Choose 5 numbers from 1 to 69 with another from 1 to 26
(3:35) Excel formula to generate many PowerBall selections
Transcript of the video:
Random with No Repeats. Well, it's been 11 years since this video.
And things have changed.
In this video, I'm going to show you how to choose eight names from a list of 26 names.
Some number of names from a list of who knows how many.
Choose five numbers from one to 69 and another from one to 26, generate many Powerball selections.
All right, so over here we have a list of 26 names, and I want to randomly choose eight of those.
Starting from the inside, we're going to sort that range.
And we're going to generate a random array of 26 numbers.
That's because there's 26 items in the list over here.
This is going to generate essentially the whole list in a new sort.
But then we only want the top eight, so I asked for the TAKE of eight.
So this formula right here will create a random list.
We press F9 to get a new list.
Now it would help, I think, to think about these as two distinct steps.
The first thing we're doing is we're shuffling the original deck of cards into a brand new list.
So we're sorting that list by a random array of 26 items.
Once we have that sorted.
Then we want only the top eight from the list, so the TAKE function is essentially dealing the first 8 items from the sorted list over here.
Now let's make this a little bit more flexible.
So over here we have a list of employees,and we want to choose five of those.
To allow for more people to be added later I'm going to make it into a table with Ctrl+T.
Click okay.
And they're going to give it a horrible name of Table1.
We should rename that.
I'll just call this Names.
That name should be short, and one word.
And then we're going to count.
So COUNTA.
And I point to the names in the table.
You see they use the table nomenclature for us.
If you named your table something different or had a different heading, it will automatically appear there.
And then the number to choose, that’s going to be 5.
All right, so we made the list into a table.
Gave the list a short but meaningful name.
Used COUNTA to figure out how many items we have.
Entered how many we wanted there in E4.
And then I'm going to do this in two steps.
We're going to do =SORTBY.
This is the range to sort.
And we're going to do a RANDARRAY of the number of items in the table.
And so every time I press F9, I reorder the list of people.
And then I want to come back and edit this.
And TAKE just the top five.
But, I'm not going to hard code the number five.
I'm going to point to cell E4.
I have to press F2 here to get into Point mode.
Now every time I press F9, I am getting a list of five.
Later, if we hire more people.
I can change this number.
And get a list of seven.
Or a list of nine.
Or, everybody with a list of 17.
Now I realize that one of the reasons why that first video was so popular is that people are trying to generate a list of lotto numbers, which is a great way to go.
If you know that you want the numbers one to 69.
You don't have to put those in the worksheet grid like we did with the names.
We can just generate the numbers one to 69 here using the SEQUENCE of 69.
And then to sort, a RANDARRAY of 69.
And then a SORTBY.
And then finally we want to TAKE the first five of those to get our lotto numbers.
And if you're playing the Powerball you need a sixth ball, the Powerball.
And that’s from a different pool of balls numbered 1 to 26.
The red balls.
So just a simple RANDBETWEEN there of 1 to 26.
The formula that's down in the YouTube description.
You can just copy into your spreadsheet, does a few more things.
Once it gets those five numbers, it then sorts them into sequence.
And joins them with commas in between.
And then finally adds the Powerball with PB, equals, RANDBETWEEN 1 and 26.
This formula is a great formula.
You just copy it from the YouTube description and paste, and then drag down, and then you will have all of your lotto numbers for the day.
Just recently I was updating the thumbnail images for the top 10, and I looked at some of these, they're really old, and I said, oh, we need to update these.
I'll be going through the others as we go.
Hey, I want to thank you for stopping by.
We'll see you next time.
For another netcast from MrExcel.
If you like this video, please, down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments in the comments below.
And things have changed.
In this video, I'm going to show you how to choose eight names from a list of 26 names.
Some number of names from a list of who knows how many.
Choose five numbers from one to 69 and another from one to 26, generate many Powerball selections.
All right, so over here we have a list of 26 names, and I want to randomly choose eight of those.
Starting from the inside, we're going to sort that range.
And we're going to generate a random array of 26 numbers.
That's because there's 26 items in the list over here.
This is going to generate essentially the whole list in a new sort.
But then we only want the top eight, so I asked for the TAKE of eight.
So this formula right here will create a random list.
We press F9 to get a new list.
Now it would help, I think, to think about these as two distinct steps.
The first thing we're doing is we're shuffling the original deck of cards into a brand new list.
So we're sorting that list by a random array of 26 items.
Once we have that sorted.
Then we want only the top eight from the list, so the TAKE function is essentially dealing the first 8 items from the sorted list over here.
Now let's make this a little bit more flexible.
So over here we have a list of employees,and we want to choose five of those.
To allow for more people to be added later I'm going to make it into a table with Ctrl+T.
Click okay.
And they're going to give it a horrible name of Table1.
We should rename that.
I'll just call this Names.
That name should be short, and one word.
And then we're going to count.
So COUNTA.
And I point to the names in the table.
You see they use the table nomenclature for us.
If you named your table something different or had a different heading, it will automatically appear there.
And then the number to choose, that’s going to be 5.
All right, so we made the list into a table.
Gave the list a short but meaningful name.
Used COUNTA to figure out how many items we have.
Entered how many we wanted there in E4.
And then I'm going to do this in two steps.
We're going to do =SORTBY.
This is the range to sort.
And we're going to do a RANDARRAY of the number of items in the table.
And so every time I press F9, I reorder the list of people.
And then I want to come back and edit this.
And TAKE just the top five.
But, I'm not going to hard code the number five.
I'm going to point to cell E4.
I have to press F2 here to get into Point mode.
Now every time I press F9, I am getting a list of five.
Later, if we hire more people.
I can change this number.
And get a list of seven.
Or a list of nine.
Or, everybody with a list of 17.
Now I realize that one of the reasons why that first video was so popular is that people are trying to generate a list of lotto numbers, which is a great way to go.
If you know that you want the numbers one to 69.
You don't have to put those in the worksheet grid like we did with the names.
We can just generate the numbers one to 69 here using the SEQUENCE of 69.
And then to sort, a RANDARRAY of 69.
And then a SORTBY.
And then finally we want to TAKE the first five of those to get our lotto numbers.
And if you're playing the Powerball you need a sixth ball, the Powerball.
And that’s from a different pool of balls numbered 1 to 26.
The red balls.
So just a simple RANDBETWEEN there of 1 to 26.
The formula that's down in the YouTube description.
You can just copy into your spreadsheet, does a few more things.
Once it gets those five numbers, it then sorts them into sequence.
And joins them with commas in between.
And then finally adds the Powerball with PB, equals, RANDBETWEEN 1 and 26.
This formula is a great formula.
You just copy it from the YouTube description and paste, and then drag down, and then you will have all of your lotto numbers for the day.
Just recently I was updating the thumbnail images for the top 10, and I looked at some of these, they're really old, and I said, oh, we need to update these.
I'll be going through the others as we go.
Hey, I want to thank you for stopping by.
We'll see you next time.
For another netcast from MrExcel.
If you like this video, please, down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments in the comments below.
