0

Hello I am looking for some help because I don't know how to go about this and have been unable to find any resources that guide me on doing what I am trying to do.

So I work for an organization that is trying to bring our shift bid process into the 21st century. I have made some progress by creating a Google Form that submits straight to a Google Sheet. I am trying to make the sheet essentially automate the process and place people according to their seniority in the company.

Here is what I have so far.

The form has three basic questions such as "Seniority Number", "1st-6th bid choice", and "an acknowledgment that their choices are correct." Upon submitting the form a macro runs that will sort the data from highest in seniority to lowest in seniority. The form has conditional formatting set up for the different shifts and I was able to get a checkbox set up to essentially blackout shifts for when we go to check and make sure the automation was correct.

Now... What I am having an issue with...

I have been unsuccessful in figuring out how to get a formula to check each bid choice and see if there is space available.

Example:

All six shifts have 4 slots available.
The first five people have their first choice as A-Days. Unfortunately, that fifth person can't be on that shift because there are already four slots filled. I then need it to check is second choice and if that shift is filled his third and so on all the way up to a sixth choice.

From there I would want it to place all of the people's names into the respective scheduling spot.

Some insight or guidance would be greatly appreciated. I know this is a lot to ask, but I would love to help my organization with this and bring some innovation here.

Here is my sheet so far: ShiftBidSheet

  • Welcome. Would you clarify how many actual staff you have? I assume the test data (13 staff) is just a sample. Regarding the 5th person, there appears to be some logical inconsistencies. You said " I then need it to check [h]is second choice and if that shift is filled, his third and so on all the way up to a sixth choice." Might this result in the 5th person getting preference over every other employee, including higher seniority, and others who listed earlier preferences. Consider "08-01": he is 5th for "A-Days", 5th for "A-Graves", 5th for "B-Swings", etc - what shift would you assign him? – Tedinoz Jul 4 at 15:00
  • So seniority is the most important. If an employee is lowest in seniority he is most likely going to get his last valid choice. For example if the first 5 people choose A days the 5th person will get their second choice. So the choices are where people want to be placed based on their preference. We have 6 shifts of four. I was trying to automate it as the form as it is submitted by employees. Does that help clarify. – Jomom Jul 6 at 18:16
  • Thank you, that is very helpful. Would you please edit your spreadsheet to show the shifts that would be assigned for a successful outcome of shifts assignment. – Tedinoz Jul 7 at 0:40
0

You are trying to automate the allocation of shifts to workers. There are several factors:

  • 6 shift types
  • six shift preferences/choices per worker
  • only four workers can be assigned to any given shift
  • workers are assigned shifts by seniority

There may be several ways to answer your question, consider this as one solution to your problem.


Sample

The table shows the allocation of the 1st to 6th shift choice, and a final column showing the actual "Assigned Shifts".

  • All formulas are entered in row 3 and copied down to the bottom of the worker choices
  • Each choice consists of 2 columns, the "SHIFT" and a running total by "Shift".

screenshot

1st choice SHIFT = +D3

  • the workers first shift choice.

1st choice COUNT = =countif($N$3:N3,N3)

  • To create a running total by shift, the first part of the range is absolute and the second part is relative; as the formula is copied down, the range expands.

2nd choice SHIFT = =if(O3<=4," ",E3)

  • if the previous choice was valid ("<4"), then a space is entered, otherwise the workers 2nd choice is selected.

2nd choice COUNT = =if(P3=" ",0,countif($P$3:P3,P3))

  • if the selection is a space, then the value is zero, otherwise a running total is created. Notice how the totals only appear for the shifts that were previous invalid (highlighted in pink).
  • The formula in the rest of the columns is a case of rinse and repeat.

3rd choice SHIFT = =if(Q3<=4," ",F3)

3rd choice COUNT = =if(R3=" ",0,countif($R$3:R3,R3))

4th choice SHIFT = =if(S3<=4," ",G3)

4th choice COUNT = =if(T3=" ",0,countif($T$3:T3,T3))

5th choice SHIFT = =if(U3<=4," ",H3)

5th choice COUNT = =if(V3=" ",0,countif($V$3:V3,V3))

6th choice SHIFT = =if(W3<=4," ",I3)

6th choice COUNT = =if(X3=" ",0,countif($X$3:X3,X3))

ASSIGNED SHIFT = =index(N3:Y3,0,(match(maxifs(N3:Y3,N3:Y3,"<5"),N3:Y3,0))-1)

  • This formula uses match to find the maximum value (maxifs) across the row where the value is "<5" (the shift is a valid choice). Match returns an index value, and the index component of the formula enables selection of the cell to the left of the matched cell - it returns the shift name.

Below the shift choices is a small group of subtotals by shift type (row 18 to 23). If count>4, the value is highlighted - though this is a visual indicator only

  • the formula is =countif($N$3:$N$15,N18)

EXAMPLE

  • In "1st Choice", 5 workers chose "A Days" and 6 workers chose "B Graves".
  • When the shift count is greater than 4, Conditional formatting is applied to highlight the unsuccessful choice. The formatting is merely a visual indicator, shifts are assigned by formula in subsequent columns.
  • In "2nd choice" and subsequent columns, if a worker's previous selection was 5th and higher, then their "2nd" (or later) choice applies.
  • The running total is calculated to make sure that the selection is not 5th or higher.

Example spreadsheet:

| improve this answer | |
  • This is immensely helpful, I think I have a good step in the direction that I would need to head for this. I like your take on it. I appreciate the help! I like that it is keeping a running total and if the first choice is over filled it begins counting in the next column. Do to the sheer size I don't think I will be able to implement this right away, but rather maybe the next time when we bid in 3 months again I will be able to! Thanks again! – Jomom Jul 8 at 1:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.