Assign a random selection to a range
 

I have a three colum spreadsheet that is 2000 rows. Row A contains case
numbers I have to select ten rows at a time and then assign a random
supervisor to the case number as follows Sup1 = 5 cases, Sup2 = 3 cases,
Sup3 = 2 cases. Is there a macro or code that I can use to apply this to
the entire range at one time? The cases must be 1 - 10, 11 - 20, 21 - 30,
etc. I cannot randomize the numbers.

Assign a random selection to a range
 

Sup1 = 5 cases, Sup2 = 3 cases,
Sup3 = 2 cases

is the above a rule you have to meet - each time Sup1 must have 5 cases and
you want to randmize the supervisors over the 10 cases based on a
distribution of 5, 3, 2. Or do you just want to randomly assign 3
supervisors to 10 cases and it doesn't matter how many each get (an example
would be sup1 = 10, sup2 = 0, sup3 = 0).

--
Regards,
Tom Ogilvy

"Tracy D." wrote in message
...
I have a three colum spreadsheet that is 2000 rows. Row A contains case
numbers I have to select ten rows at a time and then assign a random
supervisor to the case number as follows Sup1 = 5 cases, Sup2 = 3 cases,
Sup3 = 2 cases. Is there a macro or code that I can use to apply this to
the entire range at one time? The cases must be 1 - 10, 11 - 20, 21 - 30,
etc. I cannot randomize the numbers.

Assign a random selection to a range
 

Yes & thanks for helping me.
each time Sup1 must have 5 cases and
I want to randmize the supervisors over the 10 cases based on a
distribution of 5, 3, 2.

Tracy


"Tom Ogilvy" wrote:

Sup1 = 5 cases, Sup2 = 3 cases,
Sup3 = 2 cases

is the above a rule you have to meet - each time Sup1 must have 5 cases and
you want to randmize the supervisors over the 10 cases based on a
distribution of 5, 3, 2. Or do you just want to randomly assign 3
supervisors to 10 cases and it doesn't matter how many each get (an example
would be sup1 = 10, sup2 = 0, sup3 = 0).

--
Regards,
Tom Ogilvy

"Tracy D." wrote in message
...
I have a three colum spreadsheet that is 2000 rows. Row A contains case
numbers I have to select ten rows at a time and then assign a random
supervisor to the case number as follows Sup1 = 5 cases, Sup2 = 3 cases,
Sup3 = 2 cases. Is there a macro or code that I can use to apply this to
the entire range at one time? The cases must be 1 - 10, 11 - 20, 21 - 30,
etc. I cannot randomize the numbers.

Assign a random selection to a range
 

assume the cases are in A1:A10

in B1 to B10 put in

=rnd()

in C1 put in the formula

=IF(RANK(B1,$B$1:$B$10)<=5,"SUP1",IF(RANK(B1,$B$1: $B$10)<=8,"SUP2","SUP3"))

then drag fill this down the column from C1 to C10.

This will change each time you recalculate, so once satisfied. select column
C and do Edit=Copy, then Edit=Paste Special = then Values. This will
replace the formula with the results being displayed.

--
Regards,
Tom Ogilvy

"Tracy D." wrote in message
...
Yes & thanks for helping me.
each time Sup1 must have 5 cases and
I want to randmize the supervisors over the 10 cases based on a
distribution of 5, 3, 2.

Tracy


"Tom Ogilvy" wrote:

Sup1 = 5 cases, Sup2 = 3 cases,
Sup3 = 2 cases

is the above a rule you have to meet - each time Sup1 must have 5 cases
and
you want to randmize the supervisors over the 10 cases based on a
distribution of 5, 3, 2. Or do you just want to randomly assign 3
supervisors to 10 cases and it doesn't matter how many each get (an
example
would be sup1 = 10, sup2 = 0, sup3 = 0).

--
Regards,
Tom Ogilvy

"Tracy D." wrote in message
...
I have a three colum spreadsheet that is 2000 rows. Row A contains
case
numbers I have to select ten rows at a time and then assign a random
supervisor to the case number as follows Sup1 = 5 cases, Sup2 = 3
cases,
Sup3 = 2 cases. Is there a macro or code that I can use to apply
this to
the entire range at one time? The cases must be 1 - 10, 11 - 20, 21 -
30,
etc. I cannot randomize the numbers.