Linking Common rows within 2 columns - fuzzy

[lailas]

Hello,

I am having trouble linking possible connections via fuzzy match within 2 columns.

For example, in the following 2 columns,

Adam (row 1) links with Alex in Name 2, which links with Alex in Name 1 (rows 2-3), thus linking to Ann, which links to Amy. I want all of those familial rows to be linked by a unique ID. In this example, all of the rows that hit the familial names are given ID 1.

Later, I want to do a fuzzy purge so that row 7 is brought in as well since Ann and Anne would be a high match.

Row #    |   Name 1   |    Name 2   |  Linked ID

1                   Adam            Alex              1

2                   Alex               Alex               1

3                   Alex               Ann                1

4                 Andrew          Andrew

5                     Amy              Ann                1

6                     Anne             Anne

7                        Ann               Ann              1

Thanks in advance for all of the help!!!

[danilang · Accepted solution]

Hi @lailas 

This is exactly the kind of situation that the Make Groups tool was designed for.  This looks through your data and groups them if they can be connected through a chain

After making the groups, the multi-row tools assigns a linked id and this is joined back to your source data to apply the linked ID to it.

The fuzzy match question is very broad and there are several ways to do it, all depending on the kinds of matches that you want to find in your data.  Check the solution to this post for an example related to names.  You should do the fuzzy match before the make groups though, since this will change the chains in your data

Dan

[lailas]

Thanks Dan- I did have the Make a Group in there following a fuzzy but didn't have that LinkedID created by the multi-row formula tool. That was definitely the missing piece!

For the fuzzy match before, I have it on purge mode but have unioned 2 sources of data together. I want to make sure- that will basically achieve a fuzzy across the 2 sources as well as amongst the columns themselves, right?

So in the case of my example, it would look between Name 1 and Name 2 and then down each columns. Alex-->Alexander would be a mach, Ann-Anne would be a mach and then the group would have Alex--Alex, Alex--Alexander, Alex--Adam, Alex--Ann, Anne-Ann, Anne-Ann???

Row #    |   Name 1   |    Name 2   |  Linked ID

1                   Adam            Alex              1

2                   Alex               Alexander               1

3                   Alex               Ann                1

4                 Andrew          Andrew

5                     Amy              Ann                1

6                     Anne             Anne

7                        Ann               Ann              1