Random samping with limitations

[wpetty]

I need some VBA assistance... I am trying to create a spreadsheet that will allow for a random selection of people for a duty schedule. The difficulty is that I need to randomize for an entire school year (38 weeks), while not assigning people with more than 1 duty per week. no consecutive weeks, and alternating morning and evening duties...

Additional rules - population size is 37, there are 3 morning duties (first requires 3 people, second requires 4, and third requires 2), and there are 3 afternoon duties (first requires 5 people, second requires 2 people, and third requires 5 people). People should have about the same number of tasks over the year...

Good luck and thanks for help

 

[kmillen]

I sorry it's not possible.

Between the morning and afternoon, there are 21 tasks. With 37 people, 5 people will do consecutive daily tasks. Additionally, 38 weeks, with 5 work days per week (I speculated) adds to 3,990 tasks. Thats an average of 107 or so tasks per person and just under 3 tasks per person per week.

I worked up a possible solution to your problem. If you're interested, e-mail me. Keeping in mind of course that it is only a demo.

My e-mail address is:
kerry.millen@logicare.com