Extreme Complicated Name Matching (Using Function/VBA) REVISED

[mikeleung110]

Extreme Complicated Name Matching (Using Function/VBA) REVISED


OLD POST:http://www.mrexcel.com/forum/excel-...on-visual-basic-applications.html#post4462510




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!!!


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.
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.





ANOTHER LINK:

TABLE:


[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]No.[/TD]
[TD]Name1[/TD]
[TD]Name2[/TD]
[TD]Problems[/TD]
[TD][/TD]
[TD]Non-Sorted Name[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[TD]SEE CHAN MA APPLE JOHN
/ SEE CHAN YO KEEL BABY(JOINT NAME)[/TD]
[TD]John, See chan ma apple
Baby, See chan yo keel[/TD]
[TD]Line Break, Comma[/TD]
[TD][/TD]
[TD]John, See chan ma apple
Baby, See chan yo keel[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

2​

[/TD]
[TD]XA,YOO / LEE_JBO(JOINT NAME)[/TD]
[TD]Xa Yoo
Jbo, Lee[/TD]
[TD]Line Break, Comma[/TD]
[TD][/TD]
[TD]Alevel Super Sgo M. J.[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

3​

[/TD]
[TD]HE, XIA CHAN
DAN, DONGWANG[/TD]
[TD]Dong, wang Dan
Xiachan He[/TD]
[TD]Name Reverse[/TD]
[TD][/TD]
[TD]Catup Mum Leo A.[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

5​

[/TD]
[TD]ALEVEL, SUPER SGO MOTHER JESSY[/TD]
[TD]Alevel Super Sgo M. J.[/TD]
[TD]Abbreviation[/TD]
[TD][/TD]
[TD]chin hu, offline
cgo kathy, online[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

6​

[/TD]
[TD]Catup, Mum Leo April[/TD]
[TD]Catup Mum Leo A.[/TD]
[TD]Abbreviation[/TD]
[TD][/TD]
[TD]CO JUSUPER[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

7​

[/TD]
[TD]COO, THAN[/TD]
[TD]THAN COO[/TD]
[TD]Name Reverse, Comma[/TD]
[TD][/TD]
[TD]CO JU-YEAH[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

8​

[/TD]
[TD]ONLINE CGO KATHY / OFFLINE CHIN HU (JOINT NAME)[/TD]
[TD]chin hu, offline
cgo kathy, online[/TD]
[TD]Name Reverse, Joint Name, Line Break[/TD]
[TD][/TD]
[TD]Dong, wang Dan
Xiachan He[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

9​

[/TD]
[TD]TAMKOO LO[/TD]
[TD]Tam Koo, Lo[/TD]
[TD]Name padded[/TD]
[TD][/TD]
[TD]JU YUPER, QO[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

10​

[/TD]
[TD]LI, MARYME[/TD]
[TD]Marymeli[/TD]
[TD]Name Reverse+Name padded, comma[/TD]
[TD][/TD]
[TD]JU-YEAH-AO[/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]

11​

[/TD]
[TD]Zoo, catup_SHAN[/TD]
[TD]Zoo shan catup[/TD]
[TD]underscore, comma[/TD]
[TD][/TD]
[TD]Mary, Co Yu Yeah[/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]

12​

[/TD]
[TD]CO-JU YEAH[/TD]
[TD]CO JU-YEAH[/TD]
[TD]Duplicated first name[/TD]
[TD][/TD]
[TD]Marymeli[/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]

13​

[/TD]
[TD]CO JU Mary YEAH[/TD]
[TD]Mary, Co Yu Yeah[/TD]
[TD]Duplicated first name with one name[/TD]
[TD][/TD]
[TD]Playu, So_Ju[/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]

14​

[/TD]
[TD]CO JU SUPER[/TD]
[TD]CO JUSUPER[/TD]
[TD]Duplicated first name with name padded[/TD]
[TD][/TD]
[TD]Roger, CO_JU-SUPER[/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD]

15​

[/TD]
[TD]CO JU SUPER, ROGER[/TD]
[TD]Roger, CO_JU-SUPER[/TD]
[TD]Duplicated first name with name padded+Name Reverse[/TD]
[TD][/TD]
[TD]SO JU YEAH[/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD]

16​

[/TD]
[TD]SO JU YEAH[/TD]
[TD]SO JU YEAH[/TD]
[TD]Duplicated Last 2 name(from No.12)[/TD]
[TD][/TD]
[TD]SO JU_YUPER[/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD]

17​

[/TD]
[TD]SO JU YUPER[/TD]
[TD]SO JU_YUPER[/TD]
[TD]Duplicated first name with last name different[/TD]
[TD][/TD]
[TD]Tam Koo, Lo[/TD]
[/TR]
[TR]
[TD]

18​

[/TD]
[TD]

18​

[/TD]
[TD]SO JU PLAY U[/TD]
[TD]Playu, So_Ju[/TD]
[TD]Duplicated first name with last name different+Name Reverse+padded[/TD]
[TD][/TD]
[TD]THAN COO[/TD]
[/TR]
[TR]
[TD]

19​

[/TD]
[TD]

19​

[/TD]
[TD]QO YUPER JU[/TD]
[TD]JU YUPER, QO[/TD]
[TD]Duplicated Last name with surname name different[/TD]
[TD][/TD]
[TD]Xa Yoo
Jbo, Lee[/TD]
[/TR]
[TR]
[TD]

20​

[/TD]
[TD]

20​

[/TD]
[TD]AO JU YEAH![/TD]
[TD]JU-YEAH-AO[/TD]
[TD]Duplicated Last name with last name different+Name Reverse+padded[/TD]
[TD][/TD]
[TD]Zoo shan catup[/TD]
[/TR]
[TR]
[TD]

21​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

22​

[/TD]
[TD][/TD]
[TD]Given Blue, I want to return Red!!!!![/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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/<acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym>/other methods?

 

[mikeleung110]

no one can deal with this difficult task?

 

[mikeleung110]

I believed that there should be a Excel MVP to solve this problem?

 

[mikeleung110]

NO Excel MVP or GURU to solve it?

 

[mikeleung110]

feel disppointed for no one can solve...

 

[mikeleung110]

nobody can try to solve it?