asked on

Excel duplicate highlight - column / rows

Please can you help me with a formula to highlight duplicates within a range.

Column B contains Names of staff
Column C contains responsibilities of staff (example of days worked)

The duplicates are all to be found in column C, however I want the first need to look for the duplicates against the value they hold in B.

Highlighted in yellow is what the formula would highlight "duplicate".

Column B will always be set out alphabetically. The formula will help me identify and then remove any duplicate instances in Column C.

ASKER CERTIFIED SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Martin Liss

Norie suggested that you should "adjust the start/finish rows as required", which means that given the data in your picture the conditional formatting formula in C2 should be =COUNTIFS($B$2:$B$11,B2,$C$2:$C$11,C2)>1

If you add or delete rows the formula will adjust itself to march.

Gazza83

ASKER

Thank you both kindly!

Martin Liss

You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
       Experts Exchange Most Valuable Expert (MVE) 2015, 2017
       Experts Exchange Top Expert Visual Basic Classic 2012 to 2018
       Experts Exchange Top Expert VBA 2018
       Experts Exchange Distinguished Expert in Excel 2018