Join 2 tables with specific fields then append?

[dsucci]

Hello Everyone,

I'm looking to join 2 tables lets say

Full name	First	Middle	Last
John j Smith	John	j	Smith
Jane Doe	Jane		Doe
Samuel Doe	Samuel		Doe

Full Name	First	Last	Country	Email
John Smith	John	Smith	US	jsmith@aol.com
Sam Doe	Sam	Doe	US	sdoe@aol.com
Jane Doe	Jane	Doe	China	jdoe@aol.com

I'm trying to join these 2 tables together then append the additional fields on the 2nd table to the first so it would look something like

Full Name	First	Middle	Last	Country	Email
John j Smith	John	j	Smith	US	jsmith@aol.com
Jane Doe	Jane		Doe	China	jdoe@aol.com
Samuel Doe	Samuel		Doe	US	sdoe@aol.com

I've tried the find & replace tool but it just seems to pick up on different people with the same last names, and the join tool doesn't pick up on a slightly different first name like Sam and Samuel, any advice is greatly appreciated.

[JaeLim]

Hi @dsucci 

For the join tool to work, you would need to create a common/ same field names from both data source.

For your example, it works when I use the first 3 letters of the full name. 

Hope this helps!

[dsucci]

Thanks for the reply JaeLim I appreciate it, and that solution does work to an extent but if what if I run into a solution where the names start with the first 3 letters and the same last name? For Instance Carmen White and Carolyn White. They would technically get matched by this join? Thanks again for your help

[JaeLim · Accepted solution]

Hi @dsucci ,

It will but the join will not work properly if you meet such an example.. its best to first list and understand all the different permutations you may be facing within your example before diving into creating a solution. 

For now, I have created a solution that will meet that specific problem though, please see attached workflow.