djphatic
12-12-2013, 05:19 AM
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.
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.