Another Excel question

[Alex]

It can be reworked so that it keys off the comma instead of spaces. This would give you "mark as duplicate if everything to the left of the comma plus the first single word after the comma is identical."

But yeah, if you have complexly different name structures it will quickly get very complex for formula creation. I wouldn't be surprised if there are some good purchasable macros or addons but a quick search didn't find any.

The "near match" stuff I found for Excel was all just someone making complex search strings through.

But like I said, I'm sure that some graceful solution will occur to me as soon as I'm on the train home and can't do anything about it.

If you want the alteration for using comma it is:

=LEFT(A15,FIND(",",A15,1)) & IF(ISERR(TRIM(LEFT(RIGHT(A15,LEN(A15)-FIND(",",A15,1)-1),FIND(" ",RIGHT(A15,LEN(A15)-FIND(",",A15,1)-1),1)))), RIGHT(A15,LEN(A15)-FIND(",",A15,1)-1),TRIM(LEFT(RIGHT(A15,LEN(A15)-FIND(",",A15,1)-1),FIND(" ",RIGHT(A15,LEN(A15)-FIND(",",A15,1)-1),1))))

This will result in

Smith, Tom Jones
Smith Jones, Tom
Smith Jones, Tom John
Smith Jones, Tom I Really Have Too Many Names But Mom Didn't Want To Insult Family Members
Wilson, Tom

Being stripped down to (for comparison as duplicate)

Smith, Tom
Smith Jones, Tom
Smith Jones, Tom
Smith Jones, Tom
Wilson, Tom


So only the third and fourth ones would get listed as a duplicate.