Conditional formatting highlighting duplicates

[Nessa31]

Hello,

I am working with a data base of addresses which I am trying to find duplicates in from new data coming in. I would like to be able to have the data base data in Sheet 1 and the new data in sheet 2. And reference those against each other (both sheets get highlighted). The addresses themselves are not uniform (spelled out vs abbreviations) so I have created a text join in column A which is just the numbers of the address from column C and the state from column D.

So I would like to highlight the rows where cell A is duplicated both sheets.

Right now I am using this formula that lines up with my data on sheet 2: =COUNTIF(Sheet1!$A$2:$A$10585,$A2)>0

And for sheet 1:
=COUNTIF(Sheet2!$A$2:$A$300,Sheet1!$A2)>0

Data examples on how they highlight:
Database:

1024,CO	1024	1024 Jump Street	CO
20,GA	20	20 South Parkway	GA
300,IN	300	300 Fake St	IN
5355, IN	5355	5355 W 69th Ave	IN

300,GA	300	300 Indo Way	GA
4900,NV	4900	4900 Export Boulevard	NV

New:

20,GA	20	20 South Padre	GA
300,GA	300	300 Indo Way	GA
5356,GA	5356	5356 Hassle	GA
4900,GA	4900	4900 Jumpover Rd	GA

This is just straight up not giving me correct highlights. ? It’s either not highlighting the data or it’s highlighting extra data. By that I mean it will highlights every instance that starts with 300 not just the 300,GA. Can someone help me out?

 

[jasonb75]

Everything that you say you have done is correct, I would suggest going back and checking the formulas.

If A2 was selected but not active when you set up the rule formula then there is a high possibility that the rule is out of alignment.

 

[Nessa31]

Thank you. I will double check and hopefully that fixes the issue!