Auto populate available shifts according to rank

[officerdon]

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

[#2 · Accepted solution]

@officerdon 

Please see how far my enclosed solution corresponds to your ideas.

You can then donate the money to a charitable institution.

[#3]

Thank you!  I will check this out.  It is greatly appreciated!@dscheikey 

[#4]

This works absolutely PERFECT!! Is there a way that the shift available can also have letters and special characters as well? We work in the hospital too so a shift would be like "20th-3H". Other than that, Incredible!! I was thinking St. Jude unless you have a request that our Department could donate to. Thanks again!

[#5]

Hi @officerdon,

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.

[#6]

That works perfect. Wish I had your knowledge on working excel like that! Question, is there a way to have the requests filled "snaking" through the order of ranks from chief to lowest...that is to say, Chief doesn't always get what he wants...so he gets first pick, then captain, then sgt. and so on. so if chief wants a range of dates, and others want the same, chief gets first pick, then captain (if not taken by chief, then Sgt. if not taken by captain and chief and so forth by snaking through the round, until all shift requests are taken). Essentially it's like a fantasy football draft, chief starts with his desired pick for the first round and it moves down. Round starts at the top (chief) and snakes through to the bottom, then starts back at the top and snakes back down.

Chief: 2nd, 3rd, 4th, 5th (gets 2nd)
Captain: 2nd, 3rd, 6th, 7th (gets 3rd)
Sgt.: 3rd, 6th, 8th, 10th (gets 6th)
Off#1: 4th, 5th, 7th, 8th (gets 4th)
Off#2: 5th, 7th, 9th (gets 5th)
and so forth

[#7]

Not even sure that possible for excel to do for the experts..? But the Twp. did authorize a $100 check for St. Jude on behalf of our PD! So that is a feel good story :)

[#8]

Hi @officerdon,

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.

[#9]

Superior work! I thank you for everything! I can't imagine how tricky that was. You sir are a blessing!

[#10]

@officerdon 

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?

[#11]

@dscheikey 

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!

[#12]

Hi@officerdon,

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.

[#13]

Works flawlessly!!! Thank you so much. Got next months OT schedule done in record time! Your time is greatly appreciated!!