Problem with IF formula

  • Thread starter Thread starter Daniel
  • Start date Start date
D

Daniel

Hi everyone

I need a fomula that will test for duplicates of records,
based on the contents of two columns, so that if a
records has xx in col1 then az in col2 then has xx in
col1 further down the sheet but ax in col2 then this is
NOT a duplicate, but if further down the sheet there is a
records with xx in col1 and then az in col2 then this one
(pr the first occurence) needs to be flagged as duplicate.

The combination of fields is the bit I can't get to work:
Been Kindly given this formula but it doesn't do what I
need it to:
=IF(AND(COUNTIF($A2:A$2000,A2)>1,COUNTIF($B2:B$2000,B2)
1),"Duplicate","")
Click to expand...

I get these results:

xx bb Duplicate
xx cc Duplicate
xx bb
dd yy Duplicate
DD FF Duplicate - This is not a duplicate record
dd yy
GG FF
DD CC Duplicate
OO CC Duplicate - Neither is this
DD CC
OO BB
CC DD Duplicate
CC DD

This formula marks them all sa duplicate:=IF(SUMPRODUCT(--
($A2:A$2000=A2),--($B2:B$2000=B2)),"Duplicate","")

Hope someone can help!

Cheers

Daniel
 
I would suggest concatenating the two cells (ie. A2 & B2
= "xxbb") and using this in the formulae.

This should get rid of your problem.
 
Back
Top