How to find Partial Duplicates of Mailing Addresses

[Sasanna]

I have one column of Addresses with partial duplicates that need to be highlighted. However, I can't do a simple COUNTIF or MATCH to find the duplicates because these are all slightly different.

Here is an example:

FIRST LAST ADDRESS STATE
Jane Doe 1001 Front St VA
Brian Doe 1001 Front Street VA
Jane Doe 1001 Front St. VA
Tim Smith 10052 Oak Branch Cir PA
Diana Li 1006 Maple Ave MO
Bob Stone 10085 Crabapple Ln AZ
Felicia Kelly 10088 Oak Branch Cir AR
Helen Pollock 101 Belvedere Avenue ND
Virginia Wolf 101 California St RI
Rich Ligume 101 California Street, Suite 1000 RI
Depack Welch 101 Chatsworth Drive CA
Depack Welch 101 Chatsworth Dr. CA

I need the solution to highlight the duplicates such as Front St, California St and Chatsworth Dr.

Thank you for your help!

 

[RonB1111]

What if you try to match on only the 1st 8 characters in the address. Use a helper column E with this formula in E2 and copied down:
=LEFT(C2,8)

Then enter this formula in F2 and copy down:
=COUNTIF(E:E,E2)

 

[Sasanna]

Thank you RonB1111! It works well except for PO Box addresses.

 

[BenMiller]

Why doesn't it work for PO boxes? Because you have PO and P.O. and POB etc.?

Maybe first do a find/replace to make them all uniform, then use RonB1111's suggestion...

 

[RonB1111]

Because the formula only looks to the 1st 8 characters to do the match, the 1st eight characters of the address "P.O. Box 1234" are P.O. Box so it's not seeing enough data to compare. If you increased the 8 in the formula to a larger number, it may solve the POBox problem but may cause regular street address to not match well. Alternatively you could use Ben Millers find/replace suggestion, or you could modify the formula in E2 to perhaps:
=IF(LEFT(A2,6)="PO Box",SUBSTITUTE(A2,"PO Box",""),IF(LEFT(A2,8)="P.O. Box",SUBSTITUTE(A2,"P.O. Box",""),LEFT(A2,8)))

 

[PaddyD]

You might also consider the fuzzy matching algorithms here:

http://www.mrexcel.com/forum/showthread.php?t=69649

...though that might be overkill.