creating a list based on multiple criteria

[RedOctoberKnight]

Hello,<o></o>
I’m currently working on a project. I’m trying tocreate a sheet that would create a separate list of employees based on multiple statuseswhen scheduling overtime.<o></o>
<o> </o>
My sheet is laid out as follows. OVERTIME NEED being in A1.<o></o>
[TABLE="class: MsoTableGrid"]
<tbody>[TR]
[TD="width: 115, bgcolor: transparent"] OVERTIME NEED<o></o>
[/TD]
[TD="width: 88, bgcolor: transparent"] AGENT<o></o>
[/TD]
[TD="width: 91, bgcolor: transparent"] HOURS<o></o>
[/TD]
[TD="width: 85, bgcolor: transparent"] STATUS<o></o>
[/TD]
[TD="width: 131, bgcolor: transparent"] HIRE DATE<o></o>
[/TD]
[TD="width: 115, bgcolor: transparent"] AWARD ORDER<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 115, bgcolor: transparent"] 1300-1900<o></o>
[/TD]
[TD="width: 88, bgcolor: transparent"] WILLIAMS<o></o>
[/TD]
[TD="width: 91, bgcolor: transparent"] 0<o></o>
[/TD]
[TD="width: 85, bgcolor: transparent"] A<o></o>
[/TD]
[TD="width: 131, bgcolor: transparent"] 1/1/1990<o></o>
[/TD]
[TD="width: 115, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 115, bgcolor: transparent"][/TD]
[TD="width: 88, bgcolor: transparent"] TORRES<o></o>
[/TD]
[TD="width: 91, bgcolor: transparent"] 0<o></o>
[/TD]
[TD="width: 85, bgcolor: transparent"] C<o></o>
[/TD]
[TD="width: 131, bgcolor: transparent"] 2/5/1998<o></o>
[/TD]
[TD="width: 115, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 115, bgcolor: transparent"][/TD]
[TD="width: 88, bgcolor: transparent"] BUMP<o></o>
[/TD]
[TD="width: 91, bgcolor: transparent"] 1230-2100<o></o>
[/TD]
[TD="width: 85, bgcolor: transparent"] B<o></o>
[/TD]
[TD="width: 131, bgcolor: transparent"] 8/15/2008<o></o>
[/TD]
[TD="width: 115, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 115, bgcolor: transparent"][/TD]
[TD="width: 88, bgcolor: transparent"] KENNEDY<o></o>
[/TD]
[TD="width: 91, bgcolor: transparent"] 0415-1245<o></o>
[/TD]
[TD="width: 85, bgcolor: transparent"] B<o></o>
[/TD]
[TD="width: 131, bgcolor: transparent"] 10/16/2015<o></o>
[/TD]
[TD="width: 115, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
<o> </o>
I’m looking to make a list of the agents in the correct "award order" in column F thatwould be based on a few work rules.<o></o>
The first rule would be STATUS. Agents are different statusbased on their schedules. Status is followed alphabetically so “Williams” wouldbe 1st in line for overtime because he is “A” status. “Bump” and “Kennedy”would be 2^nd and 3^rd because they are “B” status and soon.<o></o>
The second rule would be based hire date. The agent who hasbeen with the company longer would be awarded overtime first and so on. <o></o>
The 3^rd rule would be hours. This is where itprobably gets tricky. I want to be able to award overtime based on time. So if “Bump”is scheduled to work 1230-2100, and I have an overtime need of 1500-1900, shewould not be able to work it because she is already working. Where “Kennedy” isscheduled to work 0415-1245 and would be available to work the overtime need of1300-1900. Agents who have a “0” in the column are not scheduled to work thatday and can work any time.<o></o>
<o> </o>
So basically when I enter in an overtime need into A2, whenit goes to award the overtime order in column F, it would look up status first,then it would look up hire date, and then it would look up hours. (all the information in Columns B,C,D, & E would be imputed manually)
So with the current example,<o></o>
I need overtime hours from 1300-1900. “Williams” would beawarded to overtime because he is on “A” status.<o></o>
If I have multiple employees on “A” status, then it would beawarded based on Hire date.<o></o>
If I have multiple employees on “B” status (which for usbasically means you are already scheduled to work that day), it would look at theirscheduled hours and award based on if they are available to work the neededovertime shift.<o></o>
<o> </o>
It’s very possible that I’m in way over my head on thisproject but if somebody actually understands what I’m talking about and canfigure out a way to do it, that would be amazing.<o></o>
Thank you to everyone who contributes to these forums. Ihave learned so much from everyone already.<o></o>
Thanks.