Removing uncommon entries between two lists of names+data

[pericyclic]

I have two long lists of names with associated data for each name from which I want to remove names that aren't common to both lists.

I need something that can take me from this:

Name

A

B

C

D

E

F

G

H

Name

A

B

C

D

E

F

G

H

Jeff Smith

x

x

x

x

x

x

x

x

Jeff Smith

x

x

x

x

x

x

x

x

Sue Jones

x

x

x

x

x

x

x

x

Mike Jones

x

x

x

x

x

x

x

x

Bill Williams

x

x

x

x

x

x

x

x

Meg Moore

x

x

x

x

x

x

x

x

Meg Moore

x

x

x

x

x

x

x

x

Joe West

x

x

x

x

x

x

x

x

To this:

Name

A

B

C

D

E

F

G

H

Name

A

B

C

D

E

F

G

H

Jeff Smith

x

x

x

x

x

x

x

x

Jeff Smith

x

x

x

x

x

x

x

x

Meg Moore

x

x

x

x

x

x

x

x

Meg Moore

x

x

x

x

x

x

x

x

If it helps, the names for list one are in column Q, and the data for these names goes from columns R to AA. The names for list two are in column AC, and the data for those names goes from columns AD to AM. First list goes down to row 4014, second list goes down to row 369

Additional wrinkle: the names in each list might not be formatted exactly the same. For instance Jeff Smith might be in both lists, but in one he could show up as Jeff R. Smith and in the other just Jeff Smith. Another possibility: Jeffrey Smith vs. Jeff Smith. However, in both lists, it is always Firstname Lastname, never Lastname comma Firstname.

I've tried several looping macros, some based around 'If InStr' and some based around the Lookup function, can't seem to get them right.

Thank you in advance for any help you can provide!

[jolivanes]

Have you by chance also asked this in other forums? If so, in all forums mention this and have a hyperlink to these other forums.

[pericyclic]

Have you by chance also asked this in other forums? If so, in all forums mention this and have a hyperlink to these other forums.

Can do. Also posted here: https://www.mrexcel.com/forum/excel-...ames-data.html

[Kgxd]

What about running a sql statement, (I'd say do a query for this) but you can always run a sql in vba. What I'm thinking is like if first name in recordset x like select left(first Nam, 4) from recordset x and then compare the last name. Don't quite know how to word this properly and the autocorrect on my phone is ****ing me off sry lol

Edit: You can use access to generate the Sql statement for you by using the find duplicates wizard