0

Best formula/function for excel matching property addresses.
I am comparing the property address with mailing address

In column A I simply want a true/false value.

https://1drv.ms/x/s!AtRzfCcgJDdpla5L5NYrz_y-Qd9y5Q?e=mqA3Jz

2
  • I'm sorry but can you please include sample data on your question? I wouldn't click on that link and don't forget about linkrot.
    – CaldeiraG
    Oct 31 '19 at 8:01
  • 1
    Add the formula to A2: =B2&C2=E2&F2
    – Lee
    Oct 31 '19 at 9:14
1

just concactenate the two cells and compare:

=B2&TRIM(C2) = E2&TRIM(F2)

enter image description here

0

THE THEORY

Name or address matching can be pretty challenging depending on the quality of data and amount of contextual information you have. At least you are only doing confirmatory matching rather than propectively taking two different address lists and seeking to cross-match common addresses!

Checking to see if the data is identical can get you some way - but you are likely to under-match because matching addresses may not be precisely the same.

Using your sample data, a naive match (identical number, identical street, only one zip code so nothing to compare) gets you:

  • 63 correctly identified as matching exactly
  • 34 correctly identified as not matching (7 of which with a blank address)
  • 26 incorrectly identified as not matching

Your biggest sources of false negatives (c 2/3rds) is just abbreviations of common words - 'court' becomes 'ct', 'boulevard' becomes 'blvd' etc.

You can do a search/replace but have to take a little care to avoid 'loops' (ave => avenue => avenuenue => avenuenuenue...) and in general to only replace whole words not parts of words and vanilla Excel is a pretty awful tool for doing this.

There are other issues (such as missing or extra spaces) though we also need spaces to identify whole words. And some general clean-up is a good idea, though your data is pretty clean.

Fairly quickly you start hitting the limits of Excel formulas, need to start doing VBA functions and end up soon in Machine Learning territory. But doing the basics will help you bring down your 20% naive error rate by c 75%.

IN PRACTICE

As a first pass I would suggest doing the following:

(1) Clean up non-printing characters etc and put spaces at start and end to simplify word recognition and make it all upper case (which is pretty much is now anyway)

(2) Make dictionary substitutions of whole-word abbreviations ("Rd" => "Road" etc) though standard Excel is poorly suited for substituting a large number of abbreviations due the the limit of 7 nested functions in one cell formula.

(3) Now strip out all the spaces and any remaining 'junk' characters

(4) Compare the two addresses.

This needs several formulas per address (because of nesting limits as well as just complexity issues).

I've attached a sheet that does this with your sample data (Excel on Google Drive).

Basic address matcher

RESULTS

enter image description here

Using this approach the number of false negaitves (rejected matches that should have been accepted) falls from 26/123 to 7/123, a drop of nearly 75%.

The dictionary of substitutions used was this:

enter image description here

The remaining 7 failures to identify the match were for:

enter image description here

Further improvement is possible, but to go much further you will want to move more towards a semantic analysis of the address rather than pure text-to-text matching or accessing master address datafiles if they are available.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.