Help identifying duplicate address on 2 sheets

[csmalley89]

I have two databases with several columns of information. I am trying to identify duplicate addresses in Sheet2 that are in Sheet1. I want to create a macro or formula that will identify if the same address appears in sheet 1 or vice versa. I have attached a sample spreadsheet of the databases I am pulling from.
Basically if Sheet2!B2 is located on Sheet1!B:B I want sheet2!C2 to say yes. And I want to be able to drag that formula down through the database.

[spitfireblue]

Please Login or Register  to view this content.

[JLGWhiz]

Based on the way your data is randomly formatted, the only columns that I would attempt to match would be those with telephone numbers and then perhaps try to check a string from the address column if there is a match on the phone number, just to confirm that it is the same location. There still could be discrepancies in the company names but it would be a step closer to matching. Here is what I had in mind.

Please Login or Register  to view this content.

Other than that, I have no ideas.

[csmalley89]

Spitfireblue This formula populates a "No" response on every row even though I know there are several duplicates.

[spitfireblue]

That is likely if they do not exactly match...
Have you tried JLGWhiz's suggestion (i.e. using the phone number instead of the address)?
Can you provide in your data an example of a duplicate (I couldn't see any in the example you provided).

[spitfireblue]

Oh... I see the error in the formula. It should be:

Please Login or Register  to view this content.

However, you will still have an issue if they do not exatly match