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>
[TD="width: 64"][/TD]
[TD="width: 77"]A[/TD]
[TD="width: 23"]B[/TD]
[TD="width: 64"]C[/TD]
[TD="width: 64"]D[/TD]
[TD="width: 70"]E[/TD]
[TD="width: 88"]F[/TD]
[TD="width: 64"]G[/TD]
[TD="width: 64"]H[/TD]
[TD="width: 64"]I[/TD]
[TD="width: 64"]J[/TD]
[TD="width: 64"]K[/TD]
[TD="width: 64"]L[/TD]
[TD="width: 64"]M[/TD]
[TD="width: 64"]N[/TD]
[TD="width: 64"]O[/TD]
[TD="width: 64"]P[/TD]
[TD="width: 64"]Q[/TD]
[TD="width: 64"]R[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="align: right"]1[/TD]
[TD="class: xl66"]Time of Day[/TD]
[TD="class: xl66"]9:00 AM[/TD]
[TD="class: xl66"]10:00 AM[/TD]
[TD="class: xl66"]11:00 AM[/TD]
[TD="class: xl66"]12:00 PM[/TD]
[TD="class: xl66"]1:00 PM[/TD]
[TD="class: xl66"]2:00 PM[/TD]
[TD="class: xl66"]3:00 PM[/TD]
[TD="class: xl66"]4:00 PM[/TD]
[TD="class: xl66"]5:00 PM[/TD]
[TD="class: xl66"]6:00 PM[/TD]
[TD="class: xl66"]7:00 PM[/TD]
[TD="class: xl66"]8:00 PM[/TD]
[TD="class: xl66"]9:00 PM[/TD]
[TD="class: xl66"]10:00 PM[/TD]
[TD="align: right"]2[/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl71"]Total Zone Demand/Hr[/TD]
[TD="class: xl71"]5[/TD]
[TD="class: xl71"]6[/TD]
[TD="class: xl71"]5[/TD]
[TD="class: xl71"]6[/TD]
[TD="class: xl71"]9[/TD]
[TD="class: xl71"]11[/TD]
[TD="class: xl71"]10[/TD]
[TD="class: xl71"]13[/TD]
[TD="class: xl71"]15[/TD]
[TD="class: xl71"]18[/TD]
[TD="class: xl71"]13[/TD]
[TD="class: xl71"]9[/TD]
[TD="class: xl71"]5[/TD]
[TD="class: xl71"]1[/TD]
[TD="class: xl71"]126[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]8[/TD]
[TD="colspan: 2"]FT Hr. Contraints[/TD]
[TD="align: right"]4[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]5[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="align: right"]8[/TD]
[TD="class: xl67"]6[/TD]
[TD="class: xl67"]9[/TD]
[TD="align: right"]6[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]7[/TD]
[TD="colspan: 2"]PT Hr. Constraints[/TD]
[TD="align: right"]7[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]9[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]10[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]11[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]12[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]13[/TD]
[TD="class: xl68"]PT[/TD]
[TD="class: xl68"]Emp. 11[/TD]
[TD="class: xl69"]1[/TD]
[TD="class: xl69"]1[/TD]
[TD="class: xl69"]1[/TD]
[TD="class: xl69"]1[/TD]
[TD="class: xl69"]1[/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl68, align: right"]5[/TD]
[TD="align: right"]14[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]15[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]16[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]17[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]18[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]19[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]20[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]21[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]22[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]23[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]24[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]25[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]26[/TD]
[TD="class: xl65"]Scheduled[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]126[/TD]
[TD="align: right"]27[/TD]
[TD="class: xl65"]Variance[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
</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:
| unavailable | ||||||||
| FT | Emp. 1 | |||||||
| After 7PM | FT | Emp. 2 | Min | Max | ||||
| FT | Emp. 3 | |||||||
| FT | Emp. 4 | |||||||
| FT | Emp. 5 | Min | Max | |||||
| FT | Emp. 6 | |||||||
| FT | Emp. 7 | |||||||
| FT | Emp. 8 | |||||||
| FT | Emp. 9 | |||||||
| FT | Emp. 10 | |||||||
| After 2PM | ||||||||
| After 3PM | PT | Emp. 12 | ||||||
| PT | Emp. 13 | |||||||
| PT | Emp. 14 | |||||||
| PT | Emp. 15 | |||||||
| PT | Emp. 16 | |||||||
| PT | Emp. 17 | |||||||
| PT | Emp. 18 | |||||||
| FT | Emp. 19 | |||||||
| PT | Emp. 20 | |||||||
| FT | Emp. 21 | |||||||
| PT | Emp. 22 | |||||||
| FT | Emp. 23 | |||||||
<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>
[TD="width: 64"][/TD]
[TD="width: 77"]A[/TD]
[TD="width: 23"]B[/TD]
[TD="width: 64"]C[/TD]
[TD="width: 64"]D[/TD]
[TD="width: 70"]E[/TD]
[TD="width: 88"]F[/TD]
[TD="width: 64"]G[/TD]
[TD="width: 64"]H[/TD]
[TD="width: 64"]I[/TD]
[TD="width: 64"]J[/TD]
[TD="width: 64"]K[/TD]
[TD="width: 64"]L[/TD]
[TD="width: 64"]M[/TD]
[TD="width: 64"]N[/TD]
[TD="width: 64"]O[/TD]
[TD="width: 64"]P[/TD]
[TD="width: 64"]Q[/TD]
[TD="width: 64"]R[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="align: right"]1[/TD]
[TD="class: xl66"]Time of Day[/TD]
[TD="class: xl66"]9:00 AM[/TD]
[TD="class: xl66"]10:00 AM[/TD]
[TD="class: xl66"]11:00 AM[/TD]
[TD="class: xl66"]12:00 PM[/TD]
[TD="class: xl66"]1:00 PM[/TD]
[TD="class: xl66"]2:00 PM[/TD]
[TD="class: xl66"]3:00 PM[/TD]
[TD="class: xl66"]4:00 PM[/TD]
[TD="class: xl66"]5:00 PM[/TD]
[TD="class: xl66"]6:00 PM[/TD]
[TD="class: xl66"]7:00 PM[/TD]
[TD="class: xl66"]8:00 PM[/TD]
[TD="class: xl66"]9:00 PM[/TD]
[TD="class: xl66"]10:00 PM[/TD]
[TD="align: right"]2[/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl71"]Total Zone Demand/Hr[/TD]
[TD="class: xl71"]5[/TD]
[TD="class: xl71"]6[/TD]
[TD="class: xl71"]5[/TD]
[TD="class: xl71"]6[/TD]
[TD="class: xl71"]9[/TD]
[TD="class: xl71"]11[/TD]
[TD="class: xl71"]10[/TD]
[TD="class: xl71"]13[/TD]
[TD="class: xl71"]15[/TD]
[TD="class: xl71"]18[/TD]
[TD="class: xl71"]13[/TD]
[TD="class: xl71"]9[/TD]
[TD="class: xl71"]5[/TD]
[TD="class: xl71"]1[/TD]
[TD="class: xl71"]126[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]8[/TD]
[TD="colspan: 2"]FT Hr. Contraints[/TD]
[TD="align: right"]4[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]5[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="align: right"]8[/TD]
[TD="class: xl67"]6[/TD]
[TD="class: xl67"]9[/TD]
[TD="align: right"]6[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]7[/TD]
[TD="colspan: 2"]PT Hr. Constraints[/TD]
[TD="align: right"]7[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]9[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]10[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]11[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]12[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]13[/TD]
[TD="class: xl68"]PT[/TD]
[TD="class: xl68"]Emp. 11[/TD]
[TD="class: xl69"]1[/TD]
[TD="class: xl69"]1[/TD]
[TD="class: xl69"]1[/TD]
[TD="class: xl69"]1[/TD]
[TD="class: xl69"]1[/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl68, align: right"]5[/TD]
[TD="align: right"]14[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]15[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]16[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]17[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]18[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]19[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]20[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]21[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]22[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]23[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]24[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]25[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]26[/TD]
[TD="class: xl65"]Scheduled[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]126[/TD]
[TD="align: right"]27[/TD]
[TD="class: xl65"]Variance[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
</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
