A more complicated solver scenario, workload distribution

Copper Contributor

Hi, I would like to use the solver plugin to automate the task of worker allocation, I have watched a few solver videos which give a basic introduction on how to use it, and I would like some advice is solver can perform in the following scenario.

The problem

  • Each year 25 customers are distributed between the 5 staff members based on a workload ability (table1)
  • Next the staff pick and equal or greater number of customers who they feel their skill set will help as a backup, As there are a greater number of Backup choices there will be some overlap. (table3)
  • Finally the staff are allocated their backup customers ensuring they meet at least their workload ability quota.(table2)

Here's a sample set of data for part 1.

Each staff members maximum workload (table1)

        Staff
Name        Workload
Bennie      5
Marchelle   3
Gita        7
Albert      4
Cecil       6

A list of customers, their pre-allocated staff member, and the Backup1 to be solved (table2)

Customers           Staff               Staff
Name        Primary Support         Backup
Ada         Gita                    ?
Awilda      Marchelle               ?   
Beula       Gita                    ?
Brenna      Bennie                  ?
Delora      Bennie                  ?
Elisha      Albert                  ?
Esta        Marchelle               ?
Janis       Albert                  ?
Jann        Gita                    ?
Julissa     Cecil                   ?
Kareem      Cecil                   ?
Lacy        Gita                    ?
Maida       Gita                    ?
Marta       Cecil                   ?
Nadene      Bennie                  ?
Nanci       Cecil                   ?
Precious    Albert                  ?
Rebbeca     Cecil                   ?
Rene        Gita                    ?
Roy         Albert                  ?
Sherie      Cecil                   ?
Soo         Bennie                  ?
Soraya      Gita                    ?
Suzi        Marchelle               ?
Wilmer      Bennie                  ?

And here is a sample set of data for the staffs choices as backup (table3)

Staff                                       Customers
Bennie      |   Ada     Elisha  Janis   Kareem      Lacy    Nanci           
Marchelle   |   Beula   Brenna  Delora  Julissa     Maida   Precious    Rene        Soo     Suzi
Gita        |   Ada     Delora  Esta    Julissa     Marta   Nadene      Roy         Soo 
Albert      |   Awilda  Esta    Jann    Julissa     Maida   Nadene      Rebbeca     Sherie  Wilmer
Cecil       |   Beula   Elisha  Esta    Lacy        Nadene  Precious    Rene        Soraya  

As you can see each staff member has picked at least or more than their current workload figures. I now need to distribute each staff member as the backup making sure that they are not already the primary support, and everyone gets a single unique backup based on the staff choices from table3.

I know this is simple enough to achieve by hand using simple trial and error however with a larger sample set of tens of staff and hundreds of customers this becomes a lot more difficult. I hope I have explained the question correctly and please feel free to give me some pointers on how I can achieve this.

0 Replies