Searching for partial match in one list against another-help needed

[NFaraci]

Hello,

Thanks in advance for any help. I'm relatively new to Excel and have been stuck on this issue for weeks. No search that I've done has yielded quite the results that I need.

Looking to search a list of addresses against another static list of addresses and return results even when there is a partial match. Would be looking for exact matches, but those will be rare, so also looking for matches based on street name. Illustrated below:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Address[/TD]
[TD][/TD]
[TD]Second Address[/TD]
[/TR]
[TR]
[TD]123 Main St[/TD]
[TD][/TD]
[TD]111 181st Apt 3B[/TD]
[/TR]
[TR]
[TD]456 Broadway[/TD]
[TD][/TD]
[TD]454 Broadway[/TD]
[/TR]
[TR]
[TD]789 Maple Ave[/TD]
[TD][/TD]
[TD]123 Main St[/TD]
[/TR]
[TR]
[TD]13 181st St [/TD]
[TD][/TD]
[TD]444 Alpine St[/TD]
[/TR]
[TR]
[TD]444 Alpine Blvd[/TD]
[TD][/TD]
[TD]Clinton & Broadway Ste 4[/TD]
[/TR]
</tbody>[/TABLE]


Work done so far was to Text To Columns on the Second Address to parse for the street name and then used various searching features against the Address list. Considered using IF statements to write a lengthy list of exclusions to NOT match to as a start. Would prefer if at all possible to not just show when there are matches (such as highlight duplicates) but to see what cells are matching to each other in the returned value.

Also looking to build a sheet that could do this ongoing with a new Second Address list pasted on a weekly basis. The list would contain ~30,000 entries each week to judge against the Address list (~700 total entries).

Thanks!

 

[Yongle]

do any street names contain numbers?

 

[NFaraci]

Yes, the street names will contain numbers, such as the 181st St example above. That had stopped me for now from being able to simply exclude any number from my search.

 

[Yongle]

Yes that is EXACTLY why I was asking.

Q Am I correct in thinking that all numbers that are street names are suffixed with one of these 4 strings "st" "nd" "rd" "th" (ie ordinal numbers) ?

Q If all ordinal number are retained and all cardinal numbers removed would that give you what you want?
Not yet sure how to achieve that (other than via VBA) but will think about it tomorrow

 

[NFaraci]

For your questions, yes the street numbers should appear in ordinal format and if we were to exclude cardinal numbers and an address had been formatted incorrectly somewhere in the 30,000 entries that would be an acceptable error for my purposes.

For this reason the first iteration of my attempt was using ISNUMBER+SEARCH and IF statements to exclude numbers. However I was struggling with the formula and the manual process of Text To Columns each time was cumbersome.

Appreciate you looking into it.

 

[Yongle]

Getting rid of the ordinals:
- Use Text-To-Columns with space as delimiter to split the text into its component parts
- Remove the values in cells containing numbers only by formula
- Concatenate the values again

How?
Like this:
- assumes data is in column A starting in A1
- select whole of column A
- click on Data tab \ Text-To-Columns with SPACE as delimiter
- ORDINALS are now in separate cells
- formula in G1 copy across to H,I,J,K,L and then down (allows for 5 spaces in cell string)
=IF(ISNUMBER(A1),"",LEFT(A1,1000)) copy (eliminates the ordinals)
- formuls in M1 copy down
=TEXTJOIN(" ",TRUE,G1:L1) (concatenates)
- copy & paste values in column M
- delete columns A-L

.

 

[NFaraci]

Thanks for that detail. I am going to do some work with that suggestion and see where it goes.

Additionally, is there a way to scrub the initial list for the full address before making the change, in case there are exact matches? Meaning, can I search all 700 addresses against the 30,000 weekly entries and return exact matches, even within a lengthier address?

Example:
123 Broadway MATCHED TO 123 Broadway Apt B, NY, NY

 

[Yongle]

Yes - use a wildcard with match
=MATCH("*"&A1&"*",Sheet2!A:A,0)

to get rid of #N/A error use
=IFERROR(MATCH("*"&A1&"*",Sheet2!A:A,0),"")

A simple formula and then sort (or filter) the result to arrive at your list