Employee shift bid scheduling assistance

[DarrenK]

Hello,

Working to assist another team at my office. This time with a more automated shift bid system. Every time a schedule adjustment comes up (which is every other month), I am asked to help make sure the shift bids go smoothly. I do this for 3 different departments and would like to help make the system a bit more user friendly and possibly prevent me from being pulled in each time.

I have my sheet as follow: ROW 1 has the start/end times of each shift available. ROW 2 has the off days listed (represented with =). Column A has all employees listed by seniority. Columns B through (in this example) F will list the order in which the employees would prefer those shifts. (EX: John might want nights so his order would be 5,4,3,2,1). Column G is set to display the numerical bid the agent is awarded based on seniority. Column H will display the actual shift listed in Row 1 based on the data in Column G. Column I of course will display the off days listed in Row 2 based on the data in Column G. (Row 3 would be blank and shaded in to separate headings from data).

[TABLE="class: grid, width: 900, align: center"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]SHIFT:[/TD]
[TD="align: center"]8-4:30[/TD]
[TD="align: center"]8-4:30[/TD]
[TD="align: center"]11-7:30[/TD]
[TD="align: center"]3:30-12[/TD]
[TD="align: center"]3:30-12[/TD]
[TD="align: center"]AWARDED[/TD]
[TD="align: center"]AWARDED[/TD]
[TD="align: center"]DAYS[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]OFF DAYS:[/TD]
[TD="align: center"]MTWTF==[/TD]
[TD="align: center"]MTWR=Y=[/TD]
[TD="align: center"]M=WTFY=[/TD]
[TD="align: center"]=TWRFY=[/TD]
[TD="align: center"]M=WRFY=[/TD]
[TD="align: center"]BID[/TD]
[TD="align: center"]SHIFT[/TD]
[TD="align: center"]OFF[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]AGENTS[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]John[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3:30-12[/TD]
[TD="align: center"]M=WRFY=[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Katie[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]8-4:30[/TD]
[TD="align: center"]MTWTF==[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Paula[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]8-4:30[/TD]
[TD="align: center"]MTWR=Y=[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]Bob[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]11-7:30[/TD]
[TD]M=WTFY=[/TD]
[/TR]
</tbody>[/TABLE]


I located an array formula as follows: =INDEX(B7:T7,MATCH(FALSE,ISNUMBER(MATCH(B7:T7,$U$1:U4,0)),0)). This was taken from Row 7, naturally. The formula, however, pulls the number from the leftmost column and displays it into the AWARDED BID column. And every line after simply references left to right and displays the numbers that way. I need a formula to locate the lowest available number in each row and display that number in Column G. The stipulation being that if the above employee has already taken the specific shift, the formula would move on to the next number sequentially. (EX: John wants nights with Tuesdays off. He has highest seniority and would therefore get it. Paula didn't get her first pick because Katie got it and she also didn't get her second pick because it went to John previously. Therefore, Paula would get her third pick.

What am I missing in the formula to make that happen? I thought maybe I needed MIN but wasn't sure where to put it in.

Secondly, in Columns H & I, I am thinking of a VLOOKUP (or maybe HLOOKUP?) to see what number went into Column G for each employee and then referencing the cells located in Rows 1 and 2 that correspond to the number in that row. (EX: John got #1 . Row 1 above John's #1 is 3:30-12 with Tuesdays off. The 3:30-12 would populate in Column H and the Tuesdays off would populate in Column I.

Would all of this be possible using formulas or is this something that would require macros? Thank you for all help in advance.

 

[MartyS]

Hi Darren

Does this help (a VBA solution)

(run the code MAIN() from within VBA)

https://www.dropbox.com/s/xnygaj380...yee shift bid scheduling assistance.xlsm?dl=0



You can alter the sheetname, where the data starts (the row containing the first name in the list) and the number of shifts (if they ever increase/decrease) by changing the values of the three CONSTs statements at the beginning.

Sub Main()


    Const Sheetname = "Sheet1"
    Const DataStartRow = 4          'i.e. the row the first person appears in (John, row 4)
    Const NoOfShifts = 5


    Dim RCntr As Integer, LastRow As Integer, Cntr As Integer, PosCntr As Integer
    Dim Pass As Boolean, FaultInData  As Boolean
    Dim Data, Results
    
    With Sheets(Sheetname)
        
        .Select
    
        LastRow = .Range("A:A").Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row


        Data = .Range("A" & DataStartRow & ":" & Chr(NoOfShifts + 65) & LastRow)
        ReDim Results(1 To NoOfShifts + 1)
        
        For RCntr = 1 To UBound(Data)
        
            Pass = False: FaultInData = False
            Do Until Pass = True Or FaultInData = True
                For PosCntr = 1 To NoOfShifts
                    For Cntr = 2 To NoOfShifts + 1
                    
                        If Data(RCntr, Cntr) < 1 Or Data(RCntr, Cntr) > NoOfShifts Then
                            FaultInData = True
                            MsgBox "Fault in data: DataRow " & RCntr & ", Name: " & Data(RCntr, 1) & ", Column " & Chr(64 + Cntr) & ", Invalid Data: [" & Data(RCntr, Cntr) & "]", vbOKOnly, "Error in Data"
                            Exit Do
                        End If
                    
                        If Data(RCntr, Cntr) = PosCntr Then
                            If Results(Cntr) <> "X" Then
                                Results(Cntr) = "X"
                                Data(RCntr, 1) = Data(RCntr, Cntr)
                                Pass = True
                                Exit Do
                             End If
                        End If
                    Next Cntr
                Next PosCntr
            Loop
            If FaultInData = True Then Exit For
        Next RCntr
        
        If FaultInData = False Then
            For RCntr = 1 To UBound(Data)
                .Range(Chr(NoOfShifts + 66) & DataStartRow + RCntr - 1).Value = Data(RCntr, 1)
            Next RCntr
        End If


    End With
    
End Sub

 

[DarrenK]

Good morning Marty,

Apologies for the lack of response as I've been out of the office all last week on an unexpected illness. Once I get everything caught up, I will try to implement this code and see if it does the trick. Thank you for your assistance.

 

[DarrenK]

I seem to be missing something in the operation of the code. Is it possible to upload the file itself so maybe you could take a first hand look at it? My actual file is a little bigger than the example provided so I'm not certain what's preventing the code from working other than my lack of experience.

 

[DarrenK]

OK it was my inexperience. The code works perfect. I setup a macro button so that way everytime new data is entered it will calculate.

Is it possible to add a similar code to another worksheet in the workbook that would do the same thing, but for a different group of people?