Link to home
Start Free TrialLog in
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
Avatar of JP
JP
Flag of United States of America image

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
Avatar of Ken Butters
Ken Butters
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of BajanPaul
BajanPaul

ASKER

Worked as suggested.  Thanks for the help!!