Microsoft Excel - Conditional formatting, finding duplicates

Question

I need to highlight duplicates in column A, but only if column B is equal to 10.

67890 10

67891 10

67891 20

67891 30

67892 10

67892 20

67894 10

67895 10

67891

In this example, what I want it to do is, as soon as I input the value on column A the cell automatically changes colour.

I have tried using sumproduct and countifs on conditional formatting, but it does not highlight the values.

Thank you in advance.

Answer 1

I found a solution; it's not "pretty", but it works. Unfortunately, it is a bit hard to describe, so I'll make an example file with explanations available, instead. In short, it uses

• a "helper cell" the locate and mark duplicates
• Excel's use a formula to determine which cells to format Conditional Formatting option to then color the original cells