Assign a random selection to a range

[Guest]

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.

 

[Tom Ogilvy]

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).

 

[Guest]

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]

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.