Random Selection

[Newdlj]

I have to come up with an OT schedule but I want it to be done randomly,
but at the same time it has to be based upon people's availability.
For instance, I have 9 people and each person has their own
availability for the week. (see the data below) If I put these people
in a range based upon their availability is there a way that I could
put their OT coverage in randomly for each week, knowing that I have to
have at least 3 people a night? Is there a way, or is there a formula
that would be sufficient. I hope that made sense.

Data:

Mon Tue Wed Thur Fri Sat Sun
DC EA KS KS JK CL JA
PH JK DC JN JN EA JK
JK JN PH JK JA JN KS
JA KS CL EA KS CL
EA LP PH PH
JN LP CL
CL LP

 

[Jay]

I have to come up with an OT schedule but I want it to be done randomly,
but at the same time it has to be based upon people's availability.
For instance, I have 9 people and each person has their own
availability for the week. (see the data below) If I put these people
in a range based upon their availability is there a way that I could
put their OT coverage in randomly for each week, knowing that I have to
have at least 3 people a night? Is there a way, or is there a formula
that would be sufficient. I hope that made sense.

Data:

Mon Tue Wed Thur Fri Sat Sun
DC EA KS KS JK CL JA
PH JK DC JN JN EA JK
JK JN PH JK JA JN KS
JA KS CL EA KS CL
EA LP PH PH
JN LP CL
CL LP

Here's one way, though it isn't elegant.

Put the data in columns A through G.

In H2 through N10 put:
=RAND()

To get the assignments for Monday, select all the data and use
Data >> Sort >> Header row >> Column H
and pick the first three people listed after sorting.

For Tuesday, do the same with Column I, etc.

For the next week, select all the random numbers and hit the F9 key to get
a new batch of random numbers.

 

[Harlan Grove]

I have to come up with an OT schedule but I want it to be done randomly,
but at the same time it has to be based upon people's availability.
For instance, I have 9 people and each person has their own
availability for the week. (see the data below) If I put these people
in a range based upon their availability is there a way that I could
put their OT coverage in randomly for each week, knowing that I have to
have at least 3 people a night? Is there a way, or is there a formula
that would be sufficient. I hope that made sense.

Data:

Mon Tue Wed Thur Fri Sat Sun
DC EA KS KS JK CL JA
PH JK DC JN JN EA JK
JK JN PH JK JA JN KS
JA KS CL EA KS CL
EA LP PH PH
JN LP CL
CL LP

Process of elimination would work fairly well for this. If you always need at
least 3 people, Saturday is immediate. What's the maximum number of days any of
these people needs to work?