Jul 23 2022 12:34 PM
Jul 23 2022 12:34 PM
Hello. Our department has at least 20-40 overtime shifts a month. We have 13 full time officers ranked by seniority (chief to regular patrolman). I am trying to create an excel sheet that (once everyone picks any of the OT shifts they can work, ranked from their most wanted to least) automatically assigns them those dates picked, BUT the senior man would get a date if a less senior (ranked) man picked it also.
In example, there are 20 shifts available, 3p-11p on the 10th through the 29th.
Chief wants the 10th, 14, and 20th
Captain wants the 14th, 21st and 22nd
Sgt. wants the 11th, 21st and 29th
Officer #1 wants the 10th, 11th, 13th and 23rd
Officer #2 wants the 13th, 23rd and 24th
Officer #3-10 same idea.
In this model, Excel would automatically assign:
Chief all 3
Captain, 21st and 22nd
Sgt. 11th and 29th
Officer #1 13th and 23rd
Officer #2 the 24th
and so on for Officer #3- #10.
Is this possible once all dates that are available are entered in a list?
I am using Microsoft 365. Thanks for your help (I'd even pay to have this setup. lol.)
Jul 23 2022 03:00 PM - edited Jul 23 2022 03:30 PMSolution
Please see how far my enclosed solution corresponds to your ideas.
You can then donate the money to a charitable institution.
Jul 24 2022 06:17 PM
Jul 25 2022 10:32 AM
I have now adjusted the table a little so that you can enter numbers or combinations of numbers/letters/special characters.
The donation thing was meant more as a joke. Nobody has to pay or donate anything to be helped here. The support is free and is done by volunteer Excel supporters. Of course the children in St. Jude would be happy about your donation. I am quite sure of that. No matter how much it is.
Jul 26 2022 04:08 AM
Jul 27 2022 12:01 PM
Jul 28 2022 01:22 PM
I created a second worksheet using the Snake variant. It was not so easy to solve with Knoff-Hoff, so I had to create an auxiliary column old-scool. Please do not delete the AM column. I have hidden it so that it does not interfere.
I hope it corresponds to your ideas.
Thank you also in the name of St. Jude. I hope it will do some good.
Jul 28 2022 06:12 PM
Jul 30 2022 04:04 AM
Good morning. I started working on the OT list. Once I clear out the initial shifts on the left and put the new requests in, the formula stops working in the "snake" book. In the "chief gets all", it shows "#NAME" as well. What am I doing wrong?
Jul 30 2022 04:13 AM
Good morning. I started working on the OT list. Once I clear out the initial shifts on the left and put the new requests in, the formula stops working in the "snake" book. In the "chief gets all", it shows "#NAME" as well. What am I doing wrong? I believe I initially replied to my post. Sorry if you get a double reply!
Jul 30 2022 01:37 PM - edited Jul 30 2022 01:57 PM
I have made a second version without modern functions like FILTER() and XMATCH(). Please try the two new spreadsheets (old scool) and see if it works for you.
Jul 31 2022 06:12 AM