Jerry from Georgia asks how to randomly distribute numbers from a list of numbers. For example, you might want to evenly distribute a list of prospects to sales reps. Episode 598 shows the somewhat convoluted method to make sure you dont get any duplicates.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I am Bill Jelen.
Today, we have a question sent in by Jerry from Georgia.
Sure he's trying to randomly assign some numbers to basically four sales reps or something like that, and he said you know the problem is is if I use RAND or RANDBETWEEN, I'm going to get duplicates.
Now there's duplicates in the list, but he said, "you know if there's three sixteens, I don't want to distribute four sixteens.
I want to make sure that everything in my original list gets assigned to a person and nothing gets assigned twice." So, my solution to this is a bit convoluted.
I insert a couple of columns.
And In the first column, I'm going to just put the RAND function =RAND And then I'm gonna put the RANK Function.
So, we'll ask for the equal rank of this value within the entire list of values.
I want to make that second parameter absolute .
So, put the dollar signs in.
Copy that down to all of our cells.
And now what's interesting here is I've now assigned a random number from 1 through 16 in Column A.
So, we'll come over here where we're going to assign our values and we're gonna use =VLOOKUP What do I want to look up?
I want to look up the number 1, but I'm not going to put the number 1 here.
I'm going to put row of A1 and use this table over here.
Basically, from A to C.
Ask for the third value.
And when I copy this down, what's going to happen is the A..
Row of A1 is going to change to row of A2, A3, A4 and so on.
What we're gonna have is every time we hit F9, the numbers from column C will be evenly but randomly distributed through column F.
So it's a little bit tougher.
You can't just use RAND or RANDBETWEEN.
You actually have to set up some random numbers.
And then rank them.
That guarantee is that you basically have one of every number from 1 through 16.
And then you can use a VLOOKUP to assign those to certain spots.
Great question from Jerry.
Bit convoluted answer.
But it should do the trick.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I am Bill Jelen.
Today, we have a question sent in by Jerry from Georgia.
Sure he's trying to randomly assign some numbers to basically four sales reps or something like that, and he said you know the problem is is if I use RAND or RANDBETWEEN, I'm going to get duplicates.
Now there's duplicates in the list, but he said, "you know if there's three sixteens, I don't want to distribute four sixteens.
I want to make sure that everything in my original list gets assigned to a person and nothing gets assigned twice." So, my solution to this is a bit convoluted.
I insert a couple of columns.
And In the first column, I'm going to just put the RAND function =RAND And then I'm gonna put the RANK Function.
So, we'll ask for the equal rank of this value within the entire list of values.
I want to make that second parameter absolute .
So, put the dollar signs in.
Copy that down to all of our cells.
And now what's interesting here is I've now assigned a random number from 1 through 16 in Column A.
So, we'll come over here where we're going to assign our values and we're gonna use =VLOOKUP What do I want to look up?
I want to look up the number 1, but I'm not going to put the number 1 here.
I'm going to put row of A1 and use this table over here.
Basically, from A to C.
Ask for the third value.
And when I copy this down, what's going to happen is the A..
Row of A1 is going to change to row of A2, A3, A4 and so on.
What we're gonna have is every time we hit F9, the numbers from column C will be evenly but randomly distributed through column F.
So it's a little bit tougher.
You can't just use RAND or RANDBETWEEN.
You actually have to set up some random numbers.
And then rank them.
That guarantee is that you basically have one of every number from 1 through 16.
And then you can use a VLOOKUP to assign those to certain spots.
Great question from Jerry.
Bit convoluted answer.
But it should do the trick.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
