Craeting a Randomized Schedule

[coryblodgett]

Ok- I have searched for about 3 hours trying to find a solution to this problem, so i finally swallowed my pride and decided to ask you guys for help. I work at a hospital and need to make a schedule. There are 3 groups- a nurse, a tech and the doc. Each doc needs two people to work with him on any given day, usually 1 nurse and one tech, randomly assigned. The group could also be composed of a doc and two nurses, but NOT a doc and two techs. I can not figure out how to make the schedule so that it includes all three groups, and randomly assigns them without repeating or creating invalid (a doc and two techs) combinations. I would also like to incorporate a "Vacation" button, where you could input what people will be on vacation and when, and have them be removed from the randomization pool on those dates. Lastly, each person maybe included into the schedule for 4 days (40 hours) in any given week. So if someone is randomly put in for mon-thurs, they should be eliminated from the pool for Friday. I know this is a lot to handle, not all of it needs to be done really, if you could just get me started on how to get the randomization to work out, i could proably figure the rest out...that being said, Im sure almost anyone of you guys could do it better and more efficiently. Thank you so much for your help!

 

[Boller]

Put the nurses in column A, put the techs in column C, put the docs in column E.

In columns B, D, & F put the formula =RAND()

Select columns A:B and sort by column B, select columns C:D and sort by column D, select columns E:F and sort by column F.
(You could skip the formula and sort of the doc columns if you wish so as to keep the docs in a fixed order.)

Each row will then contain a random selection of nurse/tech/doc.

This doesn't allow for more than one nurse for each doc, and doesn't take into account the 4-day rule, but it might get you started. VBA will probably be necessary to meet these requirements.

PS : I haven't looked at the link you provided.