I'm trying to figure out conditional formatting to compare time logs, looking for outliers. 3 columns date, startTime, endTime to be compared against 3 similar columns (Trying to highlight team members who worked alone during their logged hours)

I feel like I am dancing around a correct solution, but can't seem to nail it. The simple version for conditional formatting that works for a single column is:
for column A: =COUNTIF($C$2:$C$9,$A2)=0
for column C: =COUNTIF($A$2:$A$10,$C2)=0

But, I am comparing 3 columns (date, startTime, endTime) from 1 person against a similar time log from different people...

Each member of a team is required to work with at least one other member at any given logged time. So, I only want the format change if the entry does not have a matching (date & start & end) from another person on the team. Technically, I have up to 8 people in a team. But I think I can manage setting additional formatting after I understand the first set of columns.

I would compare by hand, but I have over 300 teams of up to 8 people. I have their logs on individual tabs, up to 8 per spreadsheet (one team). I added a total's sheet placing date, start, and end for person 1 in in columns a,b,c and person 2 in e,f,g etc....

I've tried many various combinations of countifs, countif with transpose, countif with join, countif with "&". The most recent version was:
=COUNTIF(transpose($G$3:$J),transpose($B3:$E))=0

But, most, like this one, only seem to highlight if the date is different. If the date is the same and the hours differ it doesn't seem to see it.

I'm pulling time logs into my totals page with arrayformulas. I tried at one point to join the data into a string in a single column as it was being pulled in, but either the dates were getting changed to strings of numbers or the data was too squished and I couldn't read it clear enough while reviewing.

| x | date A | Start B | End C | D | date E | Start F | End G |
|:-:|:-------:|:-------:|:-----:|:-:|:-------:|:-------:|:-----:|
| *| 9/7/22 | 10am | 11am | | 9/7/22 | 2pm | 3pm |
| | 9/7/22 | 2pm | 3pm | | 9/10/22 | 2pm | 4pm |
| *| 9/8/22 | 2pm | 3pm | | 10/2/22 | 10am | 11am |
| | 9/10/22 | 2pm | 4pm | *| 10/3/22 | 9am | 11am |
| | 10/2/22 | 10am | 11am | | | | |
* = Highlight where not found in other lists

I tried an apps script, but had to authorize it for every spreadsheet hiding the functions from users not on review committee (roughly 300 spreadsheets). The totals page will be hidden from the people logging their hours. I will not be reviewing the hours worked, there is a committee of 3 reviewing. I didn't want all 3 to have to authorize 300 spreadsheets.

//??????????????????????????????????????????????????????????
TLDR: Please help me build a conditional format formula like:
(for column A: =COUNTIF($C$2:$C$9,$A2)=0)
that will take the combined values of 3 cells ABC and highlight any row that doesn't have a matching combined EFG.
//???????????????????????????????????????????????????????????

Any help would be greatly appreciated. Thanks!

CodePudding user response：

If you could create a new column with this formula that joins E,F and G:

=arrayformula(IF(E2:E="","",E2:E&"|"&F2:F&"|"&G2:G))

I've set it in H:

Obviously you can hide that column, and yes, it is posible to do all inside the conditional formatting itself without that column but calculations will slow down a lot. You can change H to another column, just be sure that you change it in the formula too. Then you can set this conditional formatting in range A2:C that joins each row and compares to that new column.

=IF($A2="","",if(iferror(match($A2&"|"&$B2&"|"&$C2,$H:$H,0),0),false,true))

See it here