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

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

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

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

membership

This solution is only available to members.

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

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.

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,$

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.

ASKER

Worked as suggested. Thanks for the help!!