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.

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." <[email protected]> wrote in message
news:[email protected]...
> 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.
>

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." <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
>
>
>

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." <[email protected]> wrote in message
news:[email protected]...
> 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." <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> > >
> >
> >
> >