davidtirosh
New Member
- Joined
- Aug 26, 2020
- Messages
- 1
- Office Version
- 2007
- Platform
- MacOS
I have two lists that include a column of addresses. I am trying to find the Addresses that appear only in list 1.
My main issue is that the addresses are entered in an inconsistent format. I am including a few examples
List 1.
162 Stanton St
242 W. 7th Ave
24 E 61St St.
544 W 165
List 2.
162 STANTON STREET
242 WEST 7 AVENUE
In this case, the 2 first addresses are the same. So If I was to look for addresses that Exist on List 1 and not list 2, I would only get
24 E 61St St.
544 W 165
My assumption is that I should change One list to use the same formatting for the address to match the other list (The second list is consistent) and then match them.
I looked at FIND and REPLACE as well as formulas using REPLACE or SUBSTITUTE unsuccessfully.
Any suggestions would be great
Thank you
My main issue is that the addresses are entered in an inconsistent format. I am including a few examples
List 1.
162 Stanton St
242 W. 7th Ave
24 E 61St St.
544 W 165
List 2.
162 STANTON STREET
242 WEST 7 AVENUE
In this case, the 2 first addresses are the same. So If I was to look for addresses that Exist on List 1 and not list 2, I would only get
24 E 61St St.
544 W 165
My assumption is that I should change One list to use the same formatting for the address to match the other list (The second list is consistent) and then match them.
I looked at FIND and REPLACE as well as formulas using REPLACE or SUBSTITUTE unsuccessfully.
Any suggestions would be great
Thank you
