Extreme Complicated Name Matching (Using Function/VBA)

[mikeleung110]

Dear all,
I encounter difficult problem that is related to matching some customer names.
I have 2 Col, Col B(Name1) is raw data Col;Col F(Non-Sorted Name) is another name Col with difficult naming pattern which is not exactly matches.


Given: Col B (Raw data Col) , Col F (i.e. non-sorted names Col)


Task: Return the names of Col C (Name2) , i.e. Given Blue, I want to return Red!!!!!


You can see the image and the image can explain all the problems!!!
ANOTHER LINK: View image: Complicated Name

 

[mikeleung110]

I wonder no one can solve/encounter this problem before? It seemed that this can be done by using VBA, but for my ability I cannot and wonder that it there anyone can take this challenge?

 

[mikeleung110]

No Excel Guru can solve this problems?

Actually I think of several task to do but it sucks:
Task 1: Remove all of the symbol like comma, underscore, and etc, leaving only string by using function: substitute

Task 2: Trim and remove all the trailing blanks and spaces, line break and etc by using function: trim, substitute

Task 1 & 2 are only work for the Names in correct naming order, e.g. Dan, Dongwang VS Dan Dong Wang

Task 3 But how about the task of NAME REVERSE?? e.g. Dan, Dong Wang VS Dong Wang Dan
OK, you may solve Task 3 by separating the Name into 3 items and then compare: First Name, Middle Name & Last Name. (i.e. First: Dan; Middle: Wang; Last Wang Dan)
OR Just compare the Last 2 Names (i.e. Dong Wang)

Task 4 However, What if the scenorio turns to NAME REVERSE+NAME PADDED? e.g. (Dan, DongWang VS Dong Wang Dan)
Excel can only separate 2 names with space/comma/underscore detection, in which detect "DongWang" is ONLY 1 FIRST NAME instead of 2 FIRST NAME
It starts to become more difficult...

Task 5 OK, Nevertheless, suppose you can finish the Task 3, how about dealing with DUPLICATED Names with 2 SAME FIRST NAME?
(e.g. San, Dong Wang VS Dan, Dong Wang)

INDEX+MATCH/VLOOKUP only returns ONE EXACT solution for only one cells, it cannot deplays more than 1 solution in one cells.

Task 6 What if the more worse thing is that, Abbreivation appears in the cells? (e.g. Dan, Dong Wang VS Dan, D.W.)
NO ONE can know whether D.W. stands for Dong Wang except only EYE BALLS?

Task 7 Duplicated 2 First Name OR Duplicated 2 Last Name (Just like Task 5, with addition of one more names)
E.g.
CO JU Mary YEAH VS Mary, Co Yu Yeah
CO JU SUPER VS CO JUSUPER

Task 8 What if ALL problems are linked together?
COMMA+Underline+Underscore+Line Break+Trailing Space+NAME REVERSE+NAME PADDED+ABBREVIATION+Duplicated 2 First Name+ Duplicated 2 Last Name

Is it a nightmare?

CAN ANY EXCEL GURU SOLVE MY PROBLEMS by using function/VBA/other methods?

 

[ww4612]

i cannot see the pattern among the blue parts and the red part. this task is difficult even for a human to solve it. If i tell you that "xananiana" is a padded name, how can you seperate it?

 

[Aladin Akyurek]

Try to re-post. A picture requires retyping for further inquiry. Attachments lists a few methods for posting sample exhibits which do not require retyping and the add-in https://app.box.com/s/soezox25h3w0q5s4rcyl is also a method for posting an exhibit.

 

[mikeleung110]

i cannot see the pattern among the blue parts and the red part. this task is difficult even for a human to solve it. If i tell you that "xananiana" is a padded name, how can you seperate it?

Haha, I don't know and wonder the solution too, and that's why I want to post my difficult problem and discuss together.

Actually it is just like a kind of "GOOGLE SEARCH" and searching names that matches with the most of the characters in the greatest amount of extent.

 

[mikeleung110]

Try to re-post. A picture requires retyping for further inquiry. Attachments lists a few methods for posting sample exhibits which do not require retyping and the add-in https://app.box.com/s/soezox25h3w0q5s4rcyl is also a method for posting an exhibit.

but can you view the picture in the URL in my first post?
Nevertheless, I will repost and follow your steps.

 

[mikeleung110]

Actually, I think this nightmare problems just like "GOOGLE SEARCH"
That means finding a string and then highlighting the true hit parts / return the greatest extent of matches answers if possible.