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

BajanPaul
BajanPaul used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
JPIT Director

Commented:
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
You can create a new column that is a concatenation of the columns you want to check as duplicates.

I added new column as column I... which is concatenation of B/C/E
Then used same logic to check for Dups in Column I.
Duplicates-Example-1-.xlsx
Top Expert 2010

Commented:
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.

Author

Commented:
Worked as suggested.  Thanks for the help!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial