# Using Excel Solver to build rosters

#### GB

##### Member
Hi All, I have been trying to use Excel Solver to write what should be a simple solution but I do need assistance to get me started.

My Goal is - create enough 28 day roster profiles (with work days and days off) that meet the following rules. Each roster line must:
1. contain 8 days off in 4 lots of day off pairs, and
2. paired days off can't be less than 3 days apart, and
3. paired days off can't be more than 6 days apart, and
4. contain a minimum of 1 weekend off, and
5. the "Total Work Days" (blank days) must be equal to or exceed the required "Daily Work Requirement"
Can someone please teach me how to achieve this. I have provided a file which shows some sample rosters. Any header with a green cell background indicates that formulas exist in that row or column.
regards
GB

#### Attachments

• 26 KB Views: 7

#### GB

##### Member
Hi All, reaching out again to see if anyone can help with the problem above. Any solver experts amongst the group?
Regards
GB

#### p45cal

##### Well-Known Member
The attached has a macro (blah) to populate cells C7:AD352 with all possible pairs of days off with the constraints of 3 to 6 day gaps before, between and after each pair of days off.
The macro first puts pairs of Os, then at the end puts a W in all the blank cells (so it's a good idea to blank ALL the cells in that range before running).
I'll let you do the filtering for 'at least one weekend off' and I don't understand point 5.
I don't know how I'd do this with Solver.

#### Attachments

• 25.4 KB Views: 4

#### GB

##### Member
Hi @p45cal thanks for your help, much appreciated.
• In relation to Point 5 above (sorry not clear) - The "W" days must not be less than the "Daily Work Requirement", therefore if too many "O" days are given on a particular day then there is not enough staff to cover the work (W).
• I opened the file but your macro (blah) is not present. Can you please resend.
regards
GB

#### p45cal

##### Well-Known Member
I messed up there! I had to rewrite:
Code:
``````Sub blah()
Set TopLeftCell = Range("C7")
rw = TopLeftCell.Row
colm = TopLeftCell.Column

TopLeftCell.Resize(346, 28).ClearContents    'line added after I knew how big the range to clear was going to need to be!

For g1 = 0 To 6
For g2 = 3 To 6
For g3 = 3 To 6
For g4 = 3 To 6
If (g1 + g2 + g3 + g4) < 21 Then    'last pair would be beyond 28 days
If (g1 + g2 + g3 + g4) > 13 Then    'would leave final gap more than 6 days
Cells(rw, colm + g1).Resize(, 2) = "O"
Cells(rw, colm + g1 + 2 + g2).Resize(, 2) = "O"
Cells(rw, colm + g1 + 2 + g2 + 2 + g3).Resize(, 2) = "O"
Cells(rw, colm + g1 + 2 + g2 + 2 + g3 + 2 + g4).Resize(, 2) = "O"
Cells(rw, "AH") = g1 + g2 + g3 + g4
rw = rw + 1
End If
End If
Next
Next
Next
Next
Range(TopLeftCell, Cells(rw - 1, colm + 27)).SpecialCells(xlCellTypeBlanks).Value = "W"
End Sub``````
Beware, it works on the active sheet.
You'll have to amend your various formulae on sheet to match the larger ranges.
In the code, g1 to g4 are the 4 gaps:
g1 OO g2 OO g3 OO g4 OO
g5 looks after itself.

#### GB

##### Member
Thanks @p45cal I will try this and see how it goes. Cheers GB

#### p45cal

##### Well-Known Member
Just noticed, the line beginning:
Cells(rw, "AH") =
shouldn't be there.