Comparing Columns of names

[StuartE]

I have two sources of data that contain a name field and i need to compare them both to identify any missing data from either set. I've run a match formula which works well across both sets and i have now generated a list of differences between the two lists. However this has highlighted that some names are in different formats in the two tables and i want to be able to exclude these to get a true list of missing data from either source.

Different formats include names with and without titles, middle names included, middle initials etc

 

[Zot]

I have two sources of data that contain a name field and i need to compare them both to identify any missing data from either set. I've run a match formula which works well across both sets and i have now generated a list of differences between the two lists. However this has highlighted that some names are in different formats in the two tables and i want to be able to exclude these to get a true list of missing data from either source.

Different formats include names with and without titles, middle names included, middle initials etc

Everyone has own imagination which can be different from person to person. If you provide sample data, you'd probably get quicker response.

 

[StuartE]

Everyone has own imagination which can be different from person to person. If you provide sample data, you'd probably get quicker response.

Example data as below, actual data contains multiple thousand names including duplicates but if in one list will always be in the same format in that list.

Ideally from the list below only Jones, James would be returned as unique to either list

Range 1
Thompson, John Frederick George
Datta, Lynn Ms
Lewis, Victoria Mrs
Al Gazi, Anwar Mr

Range 2
Lewis, Victoria J.
Thompson, John Mr
Datta, Lynn
Al-Gazi, Anwar Mr
Jones, James

 

[HighAndWilder]

Example data as below, actual data contains multiple thousand names including duplicates but if in one list will always be in the same format in that list.

Ideally from the list below only Jones, James would be returned as unique to either list

Range 1
Thompson, John Frederick George
Datta, Lynn Ms
Lewis, Victoria Mrs
Al Gazi, Anwar Mr

Range 2
Lewis, Victoria J.
Thompson, John Mr
Datta, Lynn
Al-Gazi, Anwar Mr
Jones, James

It is very rare for a large list of names to be unique.

 

[hagia_sofia]

Hello, as there might be many possibilities of how the data is structured, it will probably be hard to solve everything via one formula, i.e. there will be need for some iterations with different approaches. As a first step you could e.g. use the following:

=IFERROR(LEFT(TRIM(A1),SEARCH("#",SUBSTITUTE(TRIM(A1)," ","#",2))-1),A1)

=IFERROR(LEFT(TRIM(B1),SEARCH("#",SUBSTITUTE(TRIM(B1)," ","#",2))-1),A1)

i.e. use this formula on both ranges and then use e.g. COUNTIFS to identify names that are on both lists (i.e. those where the count will be > 0; there still might be neccessary to check those results). Where the result of COUNTIFS will be 0, further investigation will be necessary (e.g. Al Gazi, Anwar Mr and Al-Gazi, Anwar Mr) where the most important thing is to identify patterns.

 

[Zot]

Example data as below, actual data contains multiple thousand names including duplicates but if in one list will always be in the same format in that list.

Ideally from the list below only Jones, James would be returned as unique to either list

Range 1
Thompson, John Frederick George
Datta, Lynn Ms
Lewis, Victoria Mrs
Al Gazi, Anwar Mr

Range 2
Lewis, Victoria J.
Thompson, John Mr
Datta, Lynn
Al-Gazi, Anwar Mr
Jones, James

Still need clarification for one to avoid possible wrong result, like:
Thompson, John Frederick George = Thompson, John Mr

Is there is possibility that in Range 1 there are
Thompson, John Steward
Thompson, John Frederick

and in Range 2 only
Thompson, John Mr