Matching Addresses

[Justinian]

I have a list of over four thousand addresses on one tab. On a second tab, I have about one thousand addresses. I need a formula that matches addresses and highlights them yellow. So if I have 123 Fake Street in cell A32 and 345 Boot Avenue in cell A33 on tab one and on tab two is only 123 Fake Street, I need 123 Fake Street to be highlighted yellow while 345 Boot Avenue stays as is. Would this be the match function? If so, how do I assemble it to scan through 4,000 addresses to find matches?

 

[Ben Nevis]

You should be able to do this with Conditional Formatting and a countif function as the condition.

 

[Justinian]

Not if there are no exact matches. For example, what if tab one says 123 Fake Street while tab two says 123 Fake St.?

 

[Ben Nevis]

There is a Mr Excel article that covers this
http://www.mrexcel.com/articles/duplicates-with-conditional-formatting.php

 

[Justinian]

No, that is not working. That does not help me with matching 123 Fake Street to 123 Fake St. #A.

 

[Ben Nevis]

With 123 Fake St in A1 and 123 Fake St. in B1 the following formula in C1 trims the full stop, compares the 2 strings and says they are the same
=A1=LEFT(B1,LEN(B1)-1)
If the differences are always of that sort you could incorporate it in the conditional formula.
If the differences occur in different positions I don't see how you can do it. You mentioned MATCH in your OP - this only works on numeric values. If the strings you are comparing are only superficially similar you will have to manipulate them in some way so you are comparing like with like.

 

[Justinian]

That is not what I mean. 123 Fake Street is not the same as 123 Fake St. #A.

 

[Ben Nevis]

If tab 1 says 123 Fake Street and tab 2 says 123 Fake St. you could try the Fuzzy Lookup Add-In for Excel which performs fuzzy matching of textual data in Excel.
Download Fuzzy Lookup Add-In for Excel from Official Microsoft Download Center
Also, try Googling "probabilistic matching in excel"
Alternatively you could use a sequence of Find and Replace operations to eliminate the differences between the entries in the two tabs For example Find St. and Replace with Street, Find the comma character and leave Replace blank to get rid of commas, repeat for full stops, question marks etc. That way you'll get rid of the differences between the way the two sets of data have been entered. Spell checking will help as well.

 

[Justinian]

Thank you. I will try fuzzy.