Ideas for solution to join multi-column names

Hello,

I wonder if anyone can help me with a workflow solution. I have two sets of data that I need to join by customer names, but in one set of data the names are not consistent.

The first set has first name and last name, one in each column. The second set has the full name in the first column, starting with the last name… sometimes with commas and sometimes without.

Some of the time people will use middle names, some of the time the main contact will appear under contact 2 instead of contact 1, while other times two contacts will be shown under contact 1.

There are of course duplicates of common names (e.g. two different ‘Smiths’ and two different ‘Johns’).

I split cells on set 2 by space after using the clean string node to remove punctuation and special characters, but that presented new problems

I’ve added some dummy data below to show what this looks like, of course not all of the data will necessarily connect - so I need to do a full join showing both outers, too. But much of it will connect if I knew how.

Any help with what to do here, would be very much appreciated.

Set1:

| Column 1   | Column 2  | Column 3    | Column 4    |
| First Name | Last Name | First Name2 | Last Name 2 |
| John       | Smith     |             |             |
| Dave       | Smith     |             |             |
| John       | Jones     |             |             |
| Robert     | Brown     | Jenny       | Brown       |
| James      | King      |             |             |
| Bertrand   | Russell   | Sarah       | Russell     |
| Deborah    | Franklin  | Benjamin    | Franklin    |

Set2:

| Column 1                 | Column 2  | Column 3 | Column 4 |
| Contact 1                | Contact 2 |          |          |
| Smith, John              |           |          |          |
| Smith, Dave              |           |          |          |
| Jones John               |           |          |          |
| Brown Campbell Robert    |           |          |          |
| Foley-King, James        |           |          |          |
| Russell Bertrand / Sarah |           |          |          |
| Franklin Benjamin        |           |          |          |

Set2 (split):

| Column 1                 | Column 2  | Column 3  | Column 4  | E |
| Contact 1                | Contact1a | Contact1b | Contact1c |   |
| Smith                    | John      |           |           |   |
| Smith                    | Dave      |           |           |   |
| Jones                    | John      |           |           |   |
| Brown Campbell Robert    | Campbell  | Robert    |           |   |
| King                     | Foley     | James     |           |   |
| Russell Bertrand / Sarah | Bertrand  | Bertrand  | Sarah     |   |
| Franklin                 | Benjamin  |           |           |   |