Matching Data from different sheets with only a partial match

[spectraflame]

Just looking for input on how to perform a lookup from one sheet to another where neither sheet has a unique field. I have a list of addresses and attempting to match to another list from our County that does not have the street address in the same format. Example would be:

Sheet1 field equals 204 20TH; Sheet2, field equals 204 20TH ST.
Sheet1 field equals 1521 29TH ST 305; Sheet2 field equals 1521 29TH ST. APT 305
Sheet1 field equals 205 10TH ST N; Sheet2 field equals 205 N. 10TH ST.

I have attempted to use VLOOKUP statements with (*) for a wildcard as well as parsing out the data into house number, street name, and secondary fields but since there is no consistency from the County data, I am not having much luck. I am not even sure what to call this type of lookup to give you a better explanation.

I appreciate your input and suggestions.

 

[JGordon11]

let
    lst8 = List.Transform(Excel.CurrentWorkbook(){[Name="Table8"]}[Content][Table8], each Text.Upper(_)),
    lst8RemovePunctuation = List.Transform(lst8, each Text.Remove(_,{".",",","-",";"})),
    lst9 = List.Transform(Excel.CurrentWorkbook(){[Name="Table9"]}[Content][Table9], each Text.Upper(_)),
    lst9RemovePunctuation = List.Transform(lst9, each Text.Remove(_,{".",",","-",";"})),
    lstProblemWords = Excel.CurrentWorkbook(){[Name="Table10"]}[Content][#"Problem Words"],
    lst8Split = List.Transform(lst8RemovePunctuation, each Text.Split(_," ")),
    lst8RemoveItems = List.Transform(lst8Split, each List.RemoveItems(_, lstProblemWords)),
    lst8Sort = List.Transform(lst8RemoveItems, each List.Sort(_)),
    lst9Split = List.Transform(lst9RemovePunctuation, each Text.Split(_," ")),
    lst9RemoveItems = List.Transform(lst9Split, each List.RemoveItems(_, lstProblemWords)),
    lst9Sort = List.Transform(lst9RemoveItems, each List.Sort(_)),
    tbl8 = Excel.CurrentWorkbook(){[Name="Table8"]}[Content],
    tbl9 = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],
    tbl8Index = Table.AddIndexColumn(tbl8, "Index", 0, 1, Int64.Type),
    tbl8Add = Table.AddColumn(tbl8Index,"In Table9 as:", each if List.PositionOf(lst9Sort,lst8Sort{[Index]}) >-1 then
         tbl9[Table9]{List.PositionOf(lst9Sort,lst8Sort{[Index]})} else "#NA"),
    tbl8Result = Table.RemoveColumns(tbl8Add,{"Index"})
in
   tbl8Result

pq add column.xlsm
	A	B	C	D	E	F	G	H	I
1	Table8		Table9		Problem Words		Table8	In Table9 as:
2	204 20TH		121 Main St.		ST		204 20TH	204 20TH ST.
3	1521 29TH ST 305		3332 Davis Rd.		AVE		1521 29TH ST 305	1521 29TH ST. APT 305
4	205 10TH ST N		205 N. 10TH ST.		RD		205 10TH ST N	205 N. 10TH ST.
5	3332 DAVIS RD		204 20TH ST.		APT		3332 DAVIS RD	3332 Davis Rd.
6	2000 Grove Ave		1521 29TH ST. APT 305				2000 Grove Ave	#NA
7
Sheet5

 

[spectraflame]

I am sorry for not responding earlier. This project was put on hold and I had to put this aside. Would you be able to explain what your code is doing? I am not familiar with Power Query. It appears the Problem Table houses data that can be used to translate STREET to ST etc? Would this table be able to have more entries? My source data used standard CASS certification addressing however the data I am trying to match with is not. I have tried to manually replace things like ROAD with RD and change the other street suffix, however my match percentage is about 44% out of about 9,000 rows Using standard match or VLOOKUP functions. I have tried to parse the data by house number and street and removing the street suffix, but there seems to be a lot of variable.

Again I appreciate your response. I will try to learn more about Power Query and how that differs from traditional VBA code.