Google Sheets – Automated Shift Bid Process

google sheetsgoogle-workspace

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

Best Answer

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".

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:

Related Solutions

Google Forms – Report Only the Last Entry Made by User

One approach is to add a status column next to the data input: for example, in L10 it would be

=if(iserror(match(H10, H11:H, 0)), "current", "obsolete")

which returns "current" if no entry below the present row has the same email. Then your query can filter out the obsolete results.

The above does not work with arrayformula. I don't think there is an arrayformula-compatible approach, because the number of comparisons is quadratic in the size of array. Here is a script solution that auto-expands: put status(H10:H) in a new column, with the following function in a script.

function status(arr) {
  flat = arr.reduce(function(a, b) { return a.concat(b); });
  var output = [];
  for (var i=0; i<flat.length; i++) {
    if (flat[i] === '') {
      output.push(['']);
    }
    else if (flat.slice(i+1).indexOf(flat[i]) === -1) {
      output.push(['current']);
    }
    else {
      output.push(['obsolete']);
    }
  }
  return output;
}

The approach I took in this answer also works reasonably well here: use unique to get unique users (identified by emails), then filter rows by email, and use vlookup to pick the data just from the last row. The result will be a neat summary table that has only the latest data.

Using your example, I build such a table starting from the cell A20. In A20, I enter

=unique(H10:H)

obtaining the list of emails. Then in B20 I enter

=vlookup(999999, filter($G$10:$K, $H$10:$H=$A20), column()+1)

and extend this formula to the range B20:D. This is the result:

+-----------------------------+-----------------+--------+-----------------+
| peter.goedtkindt@school.edu | SpecializationB | 2016.2 | changed my mind |
| student1@school.edu         | SpecializationB | 2016.1 |                 |
| studnent2@school.edu        | SpecializationC | 2016.1 |                 |
+-----------------------------+-----------------+--------+-----------------+

(I don't know if you needed comments, but I included them anyway.) Using column()+1 as the last parameter of vlookup allows the formula to pick the relevant column, after vlookup with absurdly large search key 999999 zooms in on the last entry.

Google-sheets – Getting total number of identical items from multiple columns

I think what you want is in Data!B24 and copied down to suit:

=sumif(C:Y,A24,D:Z)

SUMIF

Best Answer

Related Solutions

Google Forms – Report Only the Last Entry Made by User

Google-sheets – Getting total number of identical items from multiple columns

Related Topic