You have 100 sales leads in column A. You need to randomly assign those leads to 10 sales reps, but you want everyone to have the same number of leads. Bill works through two different methods in this episode.
Transcript of the video:
MrExcel podcast is sponsored by "Easy-XL"!
Learn Excel from MrExcel podcast, episode 1762 - Randomly Distribute to 10 People!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question is sent by earthworm82 @YouTube.
Has a hundred rows of data, wants to distribute them among 10 people randomly!
Randomly.
OK, well one way to go, type the 10 people over here, there's my list, and then =INDEX . Alright, the list of people, I'm going to select that list, I'm going to press F4 to put the $ in, those are really important.
And then RANDBETWEEN(1, and then the number of people in the list, there's 10 people, so hardcode that right there.
CTRL+Enter and double click to copy that down.
OK, now one problem that we're going to have, any time that you change something in this list, the whole thing is going to recalculate, so, you want to copy those and Paste Special Values.
So CTRL+C to copy, and then we'll right-click and paste Values.
Now, it's still not going to be perfect because it's random, so some people might show up 9 times, some people might show up 11 times, let's see just how bad it is.
Insert, PivotTable, will create it right here in an Existing Worksheet, and I'm going to take the "Assign To?" to Row Labels, and "Assign To?" to values.
Ooooo, CiCi is going to be real upset, she only got 6 of these leads, and Hi and Ike got 14, wow, I never would have thought it would have been that bad.
Alright, so that method is not going to work at all, BAM, let's get rid of it.
Alright, let's do this =INDEX of this list over here, F4, comma, the MOD of ROW, ,10)+1 , CiCi.
And what this should do for us is, it should just give us the whole list over and over and over again.
And we're actually starting there with CC, but that's okay because we should end up with Bo, and we do.
Alright, now this is not randomly assigning them, but what it is doing is, it's making sure that everybody is in there an identical number of times.
So I'm going to separate this a couple of spaces, I'm going to add a new column here called Rand, =RAND and shoot that down, it signs everyone a random number.
I'm now going to sort by that column, Data, A to Z. Alright so, that randomly sorted everybody, can delete this, and delete these.
And we now have 100 leads, everyone is randomly assigned to a different person.
And if we do the PivotTable test again this time, with "Assign To?" in Row Labels, and "Assign To?" in values, BAM, everyone has exactly 10, that's the way to go!
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 1762 - Randomly Distribute to 10 People!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question is sent by earthworm82 @YouTube.
Has a hundred rows of data, wants to distribute them among 10 people randomly!
Randomly.
OK, well one way to go, type the 10 people over here, there's my list, and then =INDEX . Alright, the list of people, I'm going to select that list, I'm going to press F4 to put the $ in, those are really important.
And then RANDBETWEEN(1, and then the number of people in the list, there's 10 people, so hardcode that right there.
CTRL+Enter and double click to copy that down.
OK, now one problem that we're going to have, any time that you change something in this list, the whole thing is going to recalculate, so, you want to copy those and Paste Special Values.
So CTRL+C to copy, and then we'll right-click and paste Values.
Now, it's still not going to be perfect because it's random, so some people might show up 9 times, some people might show up 11 times, let's see just how bad it is.
Insert, PivotTable, will create it right here in an Existing Worksheet, and I'm going to take the "Assign To?" to Row Labels, and "Assign To?" to values.
Ooooo, CiCi is going to be real upset, she only got 6 of these leads, and Hi and Ike got 14, wow, I never would have thought it would have been that bad.
Alright, so that method is not going to work at all, BAM, let's get rid of it.
Alright, let's do this =INDEX of this list over here, F4, comma, the MOD of ROW, ,10)+1 , CiCi.
And what this should do for us is, it should just give us the whole list over and over and over again.
And we're actually starting there with CC, but that's okay because we should end up with Bo, and we do.
Alright, now this is not randomly assigning them, but what it is doing is, it's making sure that everybody is in there an identical number of times.
So I'm going to separate this a couple of spaces, I'm going to add a new column here called Rand, =RAND and shoot that down, it signs everyone a random number.
I'm now going to sort by that column, Data, A to Z. Alright so, that randomly sorted everybody, can delete this, and delete these.
And we now have 100 leads, everyone is randomly assigned to a different person.
And if we do the PivotTable test again this time, with "Assign To?" in Row Labels, and "Assign To?" in values, BAM, everyone has exactly 10, that's the way to go!
Hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
