I have a table that looks like the one below with Work Order # in descending order:
[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]Employee Work Order #[/TD]
[TD="align: center"]Employee Date Worked[/TD]
[TD="align: center"]Employee Name[/TD]
[TD="align: center"]Employee Total Hours[/TD]
[TD="align: center"]Vehicle Work Order #[/TD]
[TD="align: center"]Vehicle Description[/TD]
[TD="align: center"]Vehicle Date Used[/TD]
[TD="align: center"]Vehicle Total Hours[/TD]
[TD="align: center"]Vehicle Operator[/TD]
[/TR]
[TR]
[TD]S11421[/TD]
[TD]9/26/17[/TD]
[TD]Jason[/TD]
[TD]6[/TD]
[TD]S11421[/TD]
[TD]Pickup Truck[/TD]
[TD]9/26/17[/TD]
[TD]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S11421[/TD]
[TD]9/26/17[/TD]
[TD]Mark[/TD]
[TD]6[/TD]
[TD]S11421[/TD]
[TD]Dump Truck[/TD]
[TD]10/10/17[/TD]
[TD]2.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S11421[/TD]
[TD]10/10/17[/TD]
[TD]Jason[/TD]
[TD]4[/TD]
[TD]S11421[/TD]
[TD]Excavator[/TD]
[TD]10/10/17[/TD]
[TD]2.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S11421[/TD]
[TD]10/10/17[/TD]
[TD]Frank[/TD]
[TD]2.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S11421[/TD]
[TD]10/10/17[/TD]
[TD]Austin[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I want to enter a name in the Operator column. The operator can be any employee as long as he meets these qualifications:
I can use an Index Match combination to return a correct value that satisfies the first three equations, but I am not sure how to handle the last qualification of not having repeats. For example, in my formula, both the dump truck and excavator would return Jason as it is the first person who satisfies the first three qualifications.
If possible, I'd prefer to just use formulas to solve this and avoid VBA. Thanks in advance!
[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]Employee Work Order #[/TD]
[TD="align: center"]Employee Date Worked[/TD]
[TD="align: center"]Employee Name[/TD]
[TD="align: center"]Employee Total Hours[/TD]
[TD="align: center"]Vehicle Work Order #[/TD]
[TD="align: center"]Vehicle Description[/TD]
[TD="align: center"]Vehicle Date Used[/TD]
[TD="align: center"]Vehicle Total Hours[/TD]
[TD="align: center"]Vehicle Operator[/TD]
[/TR]
[TR]
[TD]S11421[/TD]
[TD]9/26/17[/TD]
[TD]Jason[/TD]
[TD]6[/TD]
[TD]S11421[/TD]
[TD]Pickup Truck[/TD]
[TD]9/26/17[/TD]
[TD]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S11421[/TD]
[TD]9/26/17[/TD]
[TD]Mark[/TD]
[TD]6[/TD]
[TD]S11421[/TD]
[TD]Dump Truck[/TD]
[TD]10/10/17[/TD]
[TD]2.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S11421[/TD]
[TD]10/10/17[/TD]
[TD]Jason[/TD]
[TD]4[/TD]
[TD]S11421[/TD]
[TD]Excavator[/TD]
[TD]10/10/17[/TD]
[TD]2.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S11421[/TD]
[TD]10/10/17[/TD]
[TD]Frank[/TD]
[TD]2.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S11421[/TD]
[TD]10/10/17[/TD]
[TD]Austin[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I want to enter a name in the Operator column. The operator can be any employee as long as he meets these qualifications:
- Same Work Order #
- Same Day Worked
- Worked at least as many hours as the vehicle was used
- Is not already an operator for another piece of equipment on that same day
I can use an Index Match combination to return a correct value that satisfies the first three equations, but I am not sure how to handle the last qualification of not having repeats. For example, in my formula, both the dump truck and excavator would return Jason as it is the first person who satisfies the first three qualifications.
If possible, I'd prefer to just use formulas to solve this and avoid VBA. Thanks in advance!