Highlighting duplicates based on multiple criteria

Select A2:D13 and use CF formula:

Formula:

Please Login or Register  to view this content.

The conditional formatting formula

=COUNTIFS($D$2:$D$13,D2,$C$2:$C$13,C2,$B$2:$B$13,"<>"&B2)

will do it. Implemented in attached file, though it's not the same data as in your post. I also fixed the typo in the column C heading.

For each row, it counts the number of occurrences in the whole data set where:

$D$2:$D$13,D2	The Date is the same
$C$2:$C$13	The Tech is the same
B$2:$B$13,"<>"&B2	The group is not the same

When the count is >0, the cell is highlighted.

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

If a member helped you solve your problem, consider adding to their reputation by clicking on the star icon addreputationiconsmall.jpg below their name.

By the way

B$2:$B$13,"<>"&B2

Have you ever done this in a COUNTIF? If not: Usually in COUNTIF, you put a range, then a value. It counts all the cells in the range that are equal to that value. However, if you want a comparison that is anything other than equal, you have to build a string. The above uses the "<>" operator and concatenates the value in B2. You can also do < > <= >=. Because you are building a string you can also do arithmetic, like

$Z$2:$Z$13,">"&(TODAY()+30)

will count dates more than 30 days in the future.

I think I've used it one other time, but I hadn't thought to apply it to an arithmetic situation. I think that application will have some added value for the current sheet I'm working on. Thank you!