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:
Code:
=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.