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