highlight if value is duplicate and corresponding cell of all other duplicates is blank

Question

I have a list of clients who contacted us. We need to get back to them.

I would like to format if:

1. They appear twice in the list (got that) and

2. We have not gotten back to them in any of their appearances in the list.

For example: if A5 is duplicated in A478, I want sheets to check cell B478 and format A5 if B478 is blank.

Answer 1

(Please adjust columns and row numbers accordingly)

The formula you need for column L (or even better columns L to M) is:

=and(COUNTIFS($L$11:$L11, $L11)>1, not($M11=""))

Please note that the way the formula is structured, will NOT highlight the first instance.

If you want the first instance/occurance to be highlighted as well, the formula would be:

=and(COUNTIFS($L$11:$L, $L11)>1, not($M11=""))