I'm trying to format a spreadsheet containing timesheet data to flag certain conditions.
Each row of data represents a single pair of clock-in and clock-out times for an employee.
There can be several rows for a single employee representing each time they switched jobs, took a break, etc.
The columns are as follows:
| A | B | C | D | E | F | G | H | I |
|---|---|---|---|---|---|---|---|---|
| First Name | Last Name | Date | Day | Start Time | End Time | Hours | Job | Notes |
I need a conditional formatting rule to highlight any row that does not have the word 'Done' in Column I Notes if the following conditions are also met:
- Column H
Jobcontains the word 'shop' or 'warehouse' anywhere in the cell contents. - There is at least one other row that has matching values for all of the following columns:
- Column A
First Name - Column B
Last Name - Column C
Date - Column H
Job
- Column A
I started with the following formula for the same First Name, Last Name and Job, but I can't figure out how to build on it for the text matches in Job and Notes:
=COUNTIF(ARRAYFORMULA($A$2:$A$983& $B$2:$B$983& $C$2:$C$983& $H$2:$H$983), $A2&$B2& $C2& $H2)>1 
