Randomly select names from a list and multiple criteria

[QIBusinessGroup]

Hello All,

I have an issue I have been trying to work on all week. Here is my issue
I need to randomly select work for 12 different agent based on a list of 4 jobs.

The work has to be spread out (i.e. 4 agent doing Task 1, 4 doing Task 2...etc)
this also includes the weekend when staffing is only 3 on Saturday and sunday. also the list has to look at the agents schedule to assign a task. Example we can have all of the 9am-6pm agents doing task on b/c after 6pm there will not be anyone left to do that work. the list changes when a new month is selected. task 1-4 will be on a hidden tab with the dates.

I hope i did not confuse anyone. example is below Thank you in advance for any assistance you are able to provide. Please feel free to email me if you have any question. What I am looking for is some suggestion and formulas on which way to go. I have tried the randbetween however it does not spread the work/list out even. Is there any way i can fine tune that function?

Nate
QIBusinessGroup


Agent Name Saturday Sunday Monday Tuesday Wednesday Thursday Friday
Agent 1 Off 2:00 - 6:00 9:00 - 7:00 9:00 - 7:00 9:00 - 7:00 9:00 - 7:00 Off
Agent 2 Off 2:00 - 6:00 9:00 - 7:00 9:00 - 7:00 9:00 - 7:00 9:00 - 7:00 Off
Agent 3 Off Off 9:00 - 6:00 9:00 - 6:00 9:00 - 6:00 9:00 - 6:00 9:00 - 6:00
Agent 4 Off Off 9:00 - 6:00 9:00 - 6:00 9:00 - 6:00 9:00 - 6:00 9:00 - 6:00
Agent 5 10:00 - 7:00 Off 9:00 - 6:00 9:00 - 6:00 9:00 - 6:00 9:00 - 6:00 9:00 - 6:00
Agent 6 Off 2:00 - 6:00 9:00 - 7:00 9:00 - 7:00 9:00 - 7:00 9:00 - 7:00 Off
Agent 7 Off Off 12:30 - 9:30 12:30 - 9:30 12:30 - 9:30 12:30 - 9:30 12:30 - 9:30
Agent 8 Off Off 11:30 - 8:30 11:30 - 8:30 11:30 - 8:30 11:30 - 8:30 11:30 - 8:30
Agent 9 Off Off 11:00 - 8:00 11:00 - 8:00 11:00 - 8:00 11:00 - 8:00 11:00 - 8:00
Agent 10 10:00 - 7:00 Off 10:00 - 9:00 10:00 - 9:00 Off Off 10:00 - 9:00
Agent 11 10:00 - 7:00 Off Off 10:00 - 7:00 10:00 - 7:00 10:00 - 7:00 10:00 - 7:00
Agent 12 Off Off 1:15 - 10:15 1:15 - 10:15 1:15 - 10:15 1:15 - 10:15 1:15 - 10:15


Select A Month
May 5


Agent Name 5/7 - 5/13 5/14 - 5/20 5/21 - 5/27 5/28 - 6/3
Agent 1 Task1 1 4
Agent 2 2 3
Agent 3 3 1
Agent 4 Task 3 4 2
Agent 5 1 3
Agent 6 2 2
Agent 7 3 1
Agent 8 Task 2 4 4
Agent 9 1 2
Agent 10 2 1
Agent 11 3 3
Agent 12 4 4