Can Excel check for Duplicity?

[persol]

Hi Everyone,


Can you help me define a formula that will check the contents of 3 or 4 cells from different columns to see if the data in that raw repeats (duplicates) anywhere else in the file (3 or 4 columns)? For instance:


Columns A Column B Column C Column D Column E or Column F

10/5/2011 A1234 90212PP7 $1,298 Duplicate 2 Duplicates

10/5/2011 A1234 90212PP7 $1,298 Duplicate

10/21/2011 Q1234 CA212PP7 $3,298 Duplicate 3 Duplicates

10/21/2011 Q1234 CA212PP7 $3,298 Duplicate

10/21/2011 Q1234 CA212PP7 $3,298 Duplicate

10/18/2011 Q1234 CA212PP7 $298

10/21/2011 B1236 DA212SW $5,008


I have been using this formula =IF(COUNTIF($B$2:$B$200,B2)>1,"Duplicate","") but this formula checks the contents of one cell in that columns. If it finds the same data in that column it returns the message "Duplicate". Any chance anybody had the same need or already developed a trusty formula?

 

[Hui]

=SUMPRODUCT(($B$2:$B$200=B2)*($C$2:$C$200=C2)*($D$2:$D$200=D2))&" Duplicates"

or

=COUNTIFS($B$2:$B$200,B2,$C$2:$C$200,C2,$D$2:$D$200,D2)&" Duplicates"

 

[persol]

Hui,


that was totally unexpected. Very clever. Thank you so much for your suggested solution. It is my new keeper.