Formula To "Score" Similar Data Entries

**cmf0106** · 10-26-2013, 11:11 AM

I am working with a database of over 3,000 homes. I am looking specifically for homes that have a mismatch between the owner address and the property address. The problem is, who ever entered data at the city was not consistent with their naming conventions and I get alot of false positives as a result.

For instance
123 Green Trail and 123 Green Trl will be reported as a mismatch, even though these two addresses are the same. Perhaps if you were to score this example the two entries would be 90%+ similar.

Would it be possible to make a new column and "score" the two previous columns using some formula or function to see how similar they are to one another? That way I can easily see which entries are too similar and filter by that instead of having to manually scan the whole database.

Thanks in advance. If I need to clarify anything please let me know.

**Mazzaropi** · 10-26-2013, 11:43 AM

Why not to use FILTER and to select the proper columns.
Through the selection box you can see all results that are similar.

This way don´t works for you?

**cmf0106** · 10-27-2013, 11:34 AM

Originally Posted by Mazzaropi

Why not to use FILTER and to select the proper columns.
Through the selection box you can see all results that are similar.

This way don´t works for you?

Originally Posted by Mazzaropi

Why not to use FILTER and to select the proper columns.
Through the selection box you can see all results that are similar.

This way don´t works for you?

This will not work. Here are some examples directly from the database. I am looking for a mismatch between the fields owner address and situs address. In the image attached illustrating this notice the properties on Loraine St. They were reported as a mismatch because in the owner address field they read "NE Loraine St" and in the situs_address field they were entered as "E Loraine ST". The only reason they are being returned as a mismatch is because how the data was entered, someone forgot to include the "N" in "NE".

Same thing for the other example, owner address "1724 N Edgewood Ter" and situs address of "1724 N Edgewood Terr". The only difference between the two is one does not include the extra "r"

If I were able to create a new column with a formula that could somehow score how similar the entries were I could filter through the false positives much quicker.

See image attached that illustrates this issue.

exam3ple.png