Hello
eral,
Thanks for reaching us. From your problem description, I understand that you want the following pattern:
S >> S >> S >> OFF >> L >> L >> OFF
It seems like your employees follow a pattern of 3 consecutive S shifts, a day off, 2 consecutive L shifts, another day off, and so forth.
The mentioned articles in my previous post can’t generate this pattern. This is due to the formula used in those articles can repeat a shift value only once whereas your desired pattern has the same shift for 3 (for S shift) or 2 (for L shift) consecutive days.
Although we can develop Excel formulas for generating your desired pattern, the formulas will be very complex. Instead, we can use VBA to generate the desired pattern in a much easier way.
However, whether we use formula or VBA, there will be a few assumptions. For example, I will develop the VBA code based on the following assumptions:
- If the First Shift value is S, only the following pattern will be used:
S >> S >> S >> OFF >> L >> L >> OFF
In other words, S >> S >> OFF >> L >> L >> OFF >> S or S >> OFF >> L >> L >> OFF >> S >> S patterns are not possible.
- If the First Shift value is L, the following pattern will be used:
L >> L >> OFF >> S >> S >> S >> OFF
In other words,
L >> OFF >> S >> S >> S >> OFF >> L pattern is not possible.
- If the First Shift value is OFF, then the following pattern will be used:
OFF >> S >> S >> S >> OFF >> L >> L
In other words,
OFF >> L >> L >> OFF >> S >> S >> S pattern is not possible.
Now, go to the
Settings sheet and set the shift options to
S,
L, and
OFF as shown in the following image:
Then go to the
Roster sheet and choose
First Shift for all employees.
Now, press
Alt + F11 to open the
Visual Basic Editor window. Click the
Insert tab and select
Module.
A module will open. Insert the following code in the module:
Code:
Sub GenerateRosterPattern()
Set roster_sheet = ThisWorkbook.Sheets("Roster") ' change sheet name to your required sheet
Set shift_range = roster_sheet.Range("F8:AJ55") ' change range address to your employee range
Set first_shift_range = roster_sheet.Range("D8:D55") ' change range address to your first shift range
Set date_range = roster_sheet.Range("F7:AJ7") 'change range address to your date range
Dim SArray() As Variant
Dim LArray() As Variant
Dim OffArray() As Variant
SArray = Array("S", "S", "S", "OFF", "L", "L", "OFF")
LArray = Array("L", "L", "OFF", "S", "S", "S", "OFF")
OffArray = Array("OFF", "S", "S", "S", "OFF", "L", "L")
Dim arr_index As Integer
For i = 1 To first_shift_range.Rows.Count
arr_index = 0
If first_shift_range.Cells(i, 1).Value = "S" Then
For j = 1 To shift_range.Columns.Count
If arr_index > 6 Then
arr_index = 0
End If
If date_range.Cells(1, j).Value <> "" Then
shift_range.Cells(i, j).Value = SArray(arr_index)
End If
arr_index = arr_index + 1
Next j
End If
If first_shift_range.Cells(i, 1).Value = "L" Then
For j = 1 To shift_range.Columns.Count
If arr_index > 6 Then
arr_index = 0
End If
If date_range.Cells(1, j).Value <> "" Then
shift_range.Cells(i, j).Value = LArray(arr_index)
End If
arr_index = arr_index + 1
Next j
End If
If first_shift_range.Cells(i, 1).Value = "OFF" Then
For j = 1 To shift_range.Columns.Count
If arr_index > 6 Then
arr_index = 0
End If
If date_range.Cells(1, j).Value <> "" Then
shift_range.Cells(i, j).Value = OffArray(arr_index)
End If
arr_index = arr_index + 1
Next j
End If
Next i
End Sub
Save the workbook as a macro-enabled workbook and then click the
Run button.
The required pattern will be generated in the Roster sheet.
I hope this solution will be helpful for you. Let us know your feedback. The sample workbook used for this solution is below.
Regards,
Seemanto Saha
ExcelDemy