Guidance on VBA Rota/Scheduler procedure/algorithm

djphatic

New Member
Joined
Dec 12, 2013
Messages
1
Hello

I am looking for some help in creating 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:

For the following week a spreadsheet contains a list of service users who require visits from staff. I am trying to come up with a way of allocating staff to visits which must take into account skill level required, gender required, staff availability and less important the distance/time to travel between visits.

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 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. As mentioned earlier the process must always make suitable pairings based on:

  • Skill Level Required
  • Gender Required
  • Staff Availability

Least important matches must 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.

Any help is greatly appreciated and I am happy to discuss further and share my existing template spreadsheet with some example data.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top