This was posted originally at StackOverflow though suggested to post here instead.
I am looking to create an Excel VBA solution that will create a rota/schedule allocating staff to service users using an algorithm. I believe there are already existing names for this kind of problem/algorithms but not entirely sure what to refer to it is.
Here is my scenario:
A colleague approached me with a problem of scheduling staff against visits required. Currently this is a manual process done every week and is labour intensive. The datasets are small, on average there would be 20 service users each requiring 2 visits a day with 5 members of staff available each day.
I have a worksheet that contains the list of staff who are available for the following week. Each row of data contains their name, gender, skill level, home postcode, day working, start time and end time. A member of staff may be listed more than once on the same day as they may be available for hours in the morning and also in the evening.
Another worksheet contains the list of service users and visits required for the following week. Each row of data contains their name, postcode, day of visit, time of visit, visit duration, gender required (i.e. must be seen by a male/female), skill level required (i.e. any, certified) and the number of staff required for that visit (i.e. 1 or 2). Again a service user may appear more than once on the same day as they may require multiple visits at different times of the day (i.e. morning and evening)
I have already created some VBA functions to deal with handling of postcodes and getting latitude/longitude co-ordinates along with a function to calculate the distance between two points as the crow flies. At present the distinct values of postcodes with there co-ordinates are being stored on a helper sheet for the VBA to reference.
What I am really looking for is guidance on how to even being creating a procedure/algorithm which will produce a suggested rota/schedule, this could be a simple table with pairings between staff and visits. The process must always make suitable pairings based on:
- Skill Level Required
- Gender Required
- Staff Availability
Least important are that matches take into consideration the distance required to travel between visits. I believe this is referred to as a Genetic Algorithm?
There may be instances where there are too many service users for the available staff and vice versa. Making use of all staff isn't necessary provided all visits are dealt with, if all visits cannot be paired due to a lack of staff availability then this needs to be listed as being unmet.
I can utilize SQL Server if need be but ultimately the solution needs to be presented in Excel to the end user, as this is what they are most familiar with.
I'm thinking the procedure/process would need to go something like this:
- For each day, sort the visits by start time
- For each visit, determine which staff members meet the gender and skill level requirement and is available on that day
- For each of the staff member determined above, are they available at the time requested?
- For each staff member that is available calculate the distance from their current location to the where they need to be
- Rank them in order of preference by distance required to travel
- Once this has been done for each day, come up with a suggested allocation of staff to visits using some kind of scoring method
I'm no VBA expert and deal with SQL Server as my day job but I am willing to try and come up with a suitable solution, just need some guidance on how best to start.