Avatar of BajanPaul
BajanPaul
 asked on

Excel help to identify duplicate values with a funciton or conditional format

Hello to all excel gurus,
I am trying to write a formula that will check 3 columns for duplicate data and return a "Duplicate" value.  I am hoping to do this with either a formula or conditional formatting.

The existing formula I have is as follows:
=IF(COUNTIF($B$1:$B1,B1)>1,"Duplicate","")

I know this will only identify duplicate values in one column, how do I expand the formula to include additional criteria.

Please see my attached excel file.

BP
Duplicates-Example.xlsx
Microsoft ExcelMicrosoft ApplicationsMicrosoft Office

Avatar of undefined
Last Comment
BajanPaul

8/22/2022 - Mon
JP

I'm not certain if it can be done with a single formula or not but I don't believe so. What I would do is place your formulas in concurrent columns K:M or wherever you want and if you really want the result in on column you could hide K:M and place a formula in "N" to merge or concatenate the data =K1 & " " & L1 & " " & M1
ASKER CERTIFIED SOLUTION
Ken Butters

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Patrick Matthews

This works for me:

1) Make all formatting "plain"

2) Select A2:H19

3) Add a formula-based Conditional Formatting rule using this formula:

=COUNTIFS($B$2:$B$19,$B2,$C$2:$C$19,$C2,$E$2:$E$19,$E2)>1

Choose formatting for this rule as desired.

Any rows within that range for which there are duplicates across all three columns will receive the formatting.
BajanPaul

ASKER
Worked as suggested.  Thanks for the help!!
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck