The title kind of states my goal. I am looking for some help to get started to solve the problem of scheduling a group of people based on demand and their individual constraints, using solver and VBA (or maybe a combo of the two?)
Here is a picture to describe what I am looking for help to create via solver/vba:
<colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2676;width:58pt" width="77"> <col style="mso-width-source:userset;mso-width-alt:791;width:17pt" width="23"> <col style="width:48pt" width="64" span="2"> <col style="mso-width-source:userset;mso-width-alt:2443;width:53pt" width="70"> <col style="mso-width-source:userset;mso-width-alt:3072;width:66pt" width="88"> <col style="width:48pt" width="64" span="15"> </colgroup><tbody>
</tbody>
Row 2 has the demand needed per hour (totals 126 hours for the day).
For each employee throughout the day, I use a 1 or 0 to represent the shift work time and lunch (1 is an hour worked and 0 is lunch).
Row 26 is the total of the 1's (hours worked).
Rows 27 is the variance row 2 and row 26.
Column A states when a person is unavailable.
Off to the right side you will see full time employees (FT) need to work between 6 and 9 hours a shift with either a 30 minute or 1 hours lunch, and PT employees need to work between 4 and 6 hours per shift (no lunch).
The part I am looking for help on is to use solver/vba to fill in cells D3:Q25 by using all the hours available (in this case 126 hours), with the least absolute variance total in row 27. My example shows 0 variance but many times there is a small absolute variance.
Thanks in advance for any help or direction.
SD
Here is a picture to describe what I am looking for help to create via solver/vba:
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | ||||
1 | Time of Day | 9:00 AM | 10:00 AM | 11:00 AM | 12:00 PM | 1:00 PM | 2:00 PM | 3:00 PM | 4:00 PM | 5:00 PM | 6:00 PM | 7:00 PM | 8:00 PM | 9:00 PM | 10:00 PM | ||||||
2 | unavailable | Total Zone Demand/Hr | 5 | 6 | 5 | 6 | 9 | 11 | 10 | 13 | 15 | 18 | 13 | 9 | 5 | 1 | 126 | ||||
3 | FT | Emp. 1 | 1 | 1 | 0 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 8 | FT Hr. Contraints | |||||||
4 | After 7PM | FT | Emp. 2 | 1 | 1 | 1 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 9 | Min | Max | |||||
5 | FT | Emp. 3 | 1 | 1 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 8 | 6 | 9 | |||||||
6 | FT | Emp. 4 | 1 | 1 | 0 | 1 | 1 | 1 | 1 | 1 | 7 | PT Hr. Constraints | |||||||||
7 | FT | Emp. 5 | 1 | 1 | 0 | 1 | 1 | 1 | 1 | 1 | 7 | Min | Max | ||||||||
8 | FT | Emp. 6 | 1 | 1 | 1 | 0 | 1 | 1 | 1 | 1 | 1 | 8 | 4 | 6 | |||||||
9 | FT | Emp. 7 | 1 | 1 | 1 | 0 | 1 | 1 | 1 | 1 | 1 | 8 | |||||||||
10 | FT | Emp. 8 | 1 | 1 | 1 | 1 | 0 | 1 | 1 | 1 | 1 | 8 | |||||||||
11 | FT | Emp. 9 | 1 | 1 | 0 | 1 | 1 | 1 | 1 | 6 | |||||||||||
12 | FT | Emp. 10 | 1 | 1 | 1 | 1 | 1 | 0 | 0 | 1 | 1 | 1 | 8 | ||||||||
13 | After 2PM | PT | Emp. 11 | 1 | 1 | 1 | 1 | 1 | 5 | ||||||||||||
14 | After 3PM | PT | Emp. 12 | 1 | 1 | 1 | 1 | 4 | |||||||||||||
15 | PT | Emp. 13 | 1 | 1 | 1 | 1 | 4 | ||||||||||||||
16 | PT | Emp. 14 | 1 | 1 | 1 | 1 | 1 | 5 | |||||||||||||
17 | PT | Emp. 15 | 1 | 1 | 1 | 1 | 4 | ||||||||||||||
18 | PT | Emp. 16 | 1 | 1 | 1 | 1 | 4 | ||||||||||||||
19 | PT | Emp. 17 | 1 | 1 | 1 | 1 | 1 | 1 | 6 | ||||||||||||
20 | PT | Emp. 18 | 1 | 1 | 1 | 1 | 1 | 5 | |||||||||||||
21 | FT | Emp. 19 | 1 | 1 | 1 | 1 | 4 | ||||||||||||||
22 | PT | Emp. 20 | 1 | 1 | 1 | 1 | 4 | ||||||||||||||
23 | FT | Emp. 21 | 1 | 1 | 1 | 1 | 4 | ||||||||||||||
24 | PT | Emp. 22 | 0 | ||||||||||||||||||
25 | FT | Emp. 23 | 0 | ||||||||||||||||||
26 | Scheduled | 5 | 6 | 5 | 6 | 9 | 11 | 10 | 13 | 15 | 18 | 13 | 9 | 5 | 1 | 126 | |||||
27 | Variance | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
<colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2676;width:58pt" width="77"> <col style="mso-width-source:userset;mso-width-alt:791;width:17pt" width="23"> <col style="width:48pt" width="64" span="2"> <col style="mso-width-source:userset;mso-width-alt:2443;width:53pt" width="70"> <col style="mso-width-source:userset;mso-width-alt:3072;width:66pt" width="88"> <col style="width:48pt" width="64" span="15"> </colgroup><tbody>
</tbody>
Row 2 has the demand needed per hour (totals 126 hours for the day).
For each employee throughout the day, I use a 1 or 0 to represent the shift work time and lunch (1 is an hour worked and 0 is lunch).
Row 26 is the total of the 1's (hours worked).
Rows 27 is the variance row 2 and row 26.
Column A states when a person is unavailable.
Off to the right side you will see full time employees (FT) need to work between 6 and 9 hours a shift with either a 30 minute or 1 hours lunch, and PT employees need to work between 4 and 6 hours per shift (no lunch).
The part I am looking for help on is to use solver/vba to fill in cells D3:Q25 by using all the hours available (in this case 126 hours), with the least absolute variance total in row 27. My example shows 0 variance but many times there is a small absolute variance.
Thanks in advance for any help or direction.
SD