Fuzzy match addresses from two different spreadsheets

[Gabbz03]

Hi,

I'm super new to this but I am trying to match two different address columns from two different spreadsheets that will spit out the match and the percentage.

I have a primary address spreadsheet that contains over 60,000 addresses but also contains the floor, unit, and suite number at the end of the address:

Primary address table:

ID_Table	Address
1	123 Milner Ave Floor 1
2	321 Argyle St Unit 2
3	322 Elgin St Floor 3
4	323 Elizabeth St Unit 3
5	324 King St Floor 32
6	325 Queen St Floor 4

I want to be able to match against my secondary table that contains approximately 2000 addresses. However, addresses in this table are messier and can contain the full address including postal code, city, punctuations, typos, etc. 

Secondary Address Table:

ID	Address
1	1000 Airport Rd, Ottawa, ON K1T 3E2
2	Bronte Park, 1293 Burloak Drive (Park Office)
3	74 Wilson Rd
4	5 Steve Street
5	83 Lorne St.
6	3423 Bay Road. N (left)

I'm expecting this sort of output:

ID_Table 1	Address_Table 1	ID_Table 2	Address_Table 2	Percentage Match
1	123 Milner Ave Floor 1	No Match	No Match	%
2	321 Argyle St Unit 2	4	Ottawa, 321 Argyle St, K2L 3B2	%
3	322 Elgin St Floor 3	5	322 Elgin St.	%
4	323 Elizabeth St Unit 3	2	Bronte Park, 323 Elizabeth St (Park Office)	%
5	324 King St Floor 32	No Match	No Match	%
6	325 Queen St Floor 4	6	325 Queen St. N (left)	%
No Match	No Match	1	1000 Airport Rd, Ottawa, ON K1T 3E2	%
No Match	No Match	3	74 Wilson Rd	%

When I use the fuzzy match tool it doesn't seem to give me the results that I expect? Any ideas?

Thanks!

[danilang]

Hi @Gabbz03 

Can you update the data in your post. The data hat you're showing in Table 2 of your output table isn't in your input Table 2

Dan

[Gabbz03]

Sorry, update made.

[chashuett]

was a solution to this issue regarding matching addresses from two separate tables answered? If so, kinldy post the result.

[atcodedog05]

Hi @chashuett 

This seems like an old post i dont think so there would be any response. Can you post a new question with sample data we can take a look into it. 

[chashuett]

thank you for you reply. please find attached two csv files, each containging one attrbute; namely, "Field1," and "Field2."

I'm looking for a way to determine how many and which addresses from file Field1 match those in Field2.

r/

Charles Huett 

[atcodedog05]

Hi @chashuett 

Here is how you can do it.

Workflow:

1. Using record id tool to set row id.

2. Using formula tool to mark the source as file1 and file2. Using another formula to create custom ID for sources like F100001 (file1) and F200001 (file2). This will be useful in fuzzy matching.

3. Using union tool to join all the rows together.

4. Using fuzzy match tool merge mode. Merge mode match only among different sources this is why source label is required. And setting match id as source record id and match value is Field as address. This will match sources record ids wherever there is a value match.

5. Using join multiple tool to perform outer join and get the source2 ids (source 2 ids on which file 2 should be matched on)

6. Using join multiple tool to perform outer join on source2 id and join the data where value has fuzzy matched.

7. Using formula tool to flag as it matched.

5. Sorting to show matched at the top.

Hope this helps : )

[chashuett]

thank you very much!

the network is a thing of beauty...getting a little teary-eyed gazing at its spendor!!

However, I'm not able to download the yxmd as posted. 

please advise.

v/r,

Chas

[atcodedog05]

Hi @chashuett 

Try this. If it doesnt try refreshing the webpage and try again to download.

Hope this helps : )

[chashuett]

yes. that worked. got it.

v/r,

Chas