Comparing Text in adjacent columns

[ADArnold]

Good Morning Group,

I am a Commercial Real Estate Broker and I have several databases wit
building tenant and owner information that I need to make mailing list
from.

To put itsimply, I have the following info in 11 adjacent columns:
eg: A(1) thru K(1) - O/T, Tenant Name, Street Address, City, State
Zip, Owner Name, Owner Address, Owner City, Owner State, & Owner Zip

I want to compare both address columns (C1 & H1) to see if they match
If they match, the tenant is likely the owner, even if the tenant nam
and the owner name is different.

For example: Dr. John Smith (Tenant Name) is at 123 Main St. (Stree
Address) and JS Inc. (Owner Name) is at 123 Main St. (Owner Address)
This is an indication that the Tenant is the actual owner. I would lik
to place in column A1 (O/T) 'O' if they match and 'T' if they don't
(Tenant and Owner are most likely different.)

I have a dozen databases totaling about 30K rows and a Macro would b
ideal. Thanks for any assistance you could provide

 

[AlfD]

Hi!

Looking at solutions without macros:

put =if(C2=H2,"O","T") in cell A2.
Copy this down to all rows.


This will work if your two street addresses are identical in al
respects. Is it a bit optimistic to assume that? Will it always say St
and not St or Street?
There might be some work to be done on the lists before you trust th
O/T analysis?

Al

 

[ADArnold]

Hi Alf,

Thanks for your very quick reply! I understand that the 2 addresse
must be exactly alike.

How about this?: Address 1 is 1234 Main Street and Address 2 is 123
Main St. Would it be possible to first remove any spaces an
punctuation and then use a string comparison for maybe the first 1
characters? This would provide a closer comparison, right?

Again, thanks for your answer!

A. D. Arnold
(Sunny) Cape Coral, FL
(e-mail address removed)

 

[ADArnold]

Hi Alf,

Thanks for your very quick reply! I understand that the 2 addresse
must be exactly alike.

How about this?: Address 1 is 1234 Main Street and Address 2 is 123
Main St. Would it be possible to first remove any spaces an
punctuation and then use a string comparison for maybe the first 1
characters? This would provide a closer comparison, right?

Again, thanks for your answer!

A. D. Arnold
(Sunny) Cape Coral, FL
(e-mail address removed)

 

[ADArnold]

Oops, sorry for the double post..

 

[AlfD]

Hi!

Yes: such things could help.

1. Ensure there are no spare spaces in the street names
The function TRIM will strip them off fore and aft. If you want to tak
them out of the whole string you could search for space and replac
with "". There is also a routine (user-defined function I think) calle
TRIMALL which a quick web search would locate. This removes surplu
spaces throughout.

2. As a real estate person, you'll be more familiar than most with th
synonyms for particular thoroughfares, I guess. Get rid of them b
using search/replace. e.g. Search for Street and replace with St, o
whatever.

3. Sorting by street order will enable you to spot anomalies.

4. =LEFT(C1,10) will give you the leftmost 10 characters in the stree
name.

5. =LOWER(C1) will remove all uppercase characters. (Probably won'
need this.)

etc

Alf

From (rainy) Englan

 

[ADArnold]

Hi Alf,

Thanks a lot for your suggestions! I will experiment with what you s
kindly submitted and try to make a "one-liner" that will strip th
spaces from the left-most 10 characters and compare the results. O
course your 1st suggestion is simplicity itself and it worked "a
advertised"!

Thanks! You have been a great help!

AA

PS: Enjoy your rain and I'll enjoy my beautiful (currently) 81 an
sunny! :