Guidance on Excel VBA resource scheduling algorithm?

Question

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:

1. For each day, sort the visits by start time
2. For each visit, determine which staff members meet the gender and skill level requirement and is available on that day
3. For each of the staff member determined above, are they available at the time requested?
4. For each staff member that is available calculate the distance from their current location to the where they need to be
5. Rank them in order of preference by distance required to travel
6. 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.

Answer 1

I have some experience with the sort of scheduling problem you present, and the technical stack you're using, and so I can speak from authority and say this is a database problem and belongs in SQL Server, not Excel!.

Assuming that these are regular visits that are already scheduled and you only need to allocate your staff to the various tasks, (and staff have to return to their home office between visits), I recommend the following process:

1. Migrate your staff list to a normalized table structure, with separate Employee and Availability tables.
2. Do the same to your service users, with Customer and Visit tables. (How you populate the visits is a different question.)
3. Create a distinct Assignment table, that reflects the assignments you give your staff. This table will include both actual shifts, and necessary non-visit assignments (such as "drive across town"). Your program will generate suggestions, but you need to allow a human to revise the list.
4. Generate a "complexity" score for each visit, by adding all the necessary requirements together. ("Must be seen by two certified women for 3 hours" may be seven points, for example.)
5. For the highest complexity value, make all assignments for your time period for all visits on that assignment. Do this one row at a time, with tie-breakers based on the visit length and start-time. Select staff who meet the needs based on availability and then some sensible sort value (seniority or "times done this shift.")
6. Mark a travel time entry for the assigned staff between the customer's location and the employee's central location.
7. Repeat from #5, above, until all visits are satisfied.
8. Return to Excel two lists, one of Customer Visits that indicates the assigned employees(s), and one of Employee assignments that notes the times each employee will be assigned. Include any highlight in both any unassigned items.

There are professional Workforce Management systems that will much of this for you, but such require several tens of thousands of dollars to purchase and assume you'll have an employee doing much of the work manually. If they were doing this in Excel by hand, any automation will be an aid, and you can improve as you go on.

(And take a look at .NET or Access to replace Excel in some instances; round-tripping data from Excel back to SQL Server is significantly harder than just making a form for a SQL server table in Access.)