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:

Row\Col	A​	B​	C​	D​	E​	F​
1​	No.	Name1	Name2	Problems		Non-Sorted Name
2​	1​	SEE CHAN MA APPLE JOHN / SEE CHAN YO KEEL BABY(JOINT NAME)	John, See chan ma apple Baby, See chan yo keel	Line Break, Comma		John, See chan ma apple Baby, See chan yo keel
3​	2​	XA,YOO / LEE_JBO(JOINT NAME)	Xa Yoo Jbo, Lee	Line Break, Comma		Alevel Super Sgo M. J.
4​	3​	HE, XIA CHAN DAN, DONGWANG	Dong, wang Dan Xiachan He	Name Reverse		Catup Mum Leo A.
5​	5​	ALEVEL, SUPER SGO MOTHER JESSY	Alevel Super Sgo M. J.	Abbreviation		chin hu, offline cgo kathy, online
6​	6​	Catup, Mum Leo April	Catup Mum Leo A.	Abbreviation		CO JUSUPER
7​	7​	COO, THAN	THAN COO	Name Reverse, Comma		CO JU-YEAH
8​	8​	ONLINE CGO KATHY / OFFLINE CHIN HU (JOINT NAME)	chin hu, offline cgo kathy, online	Name Reverse, Joint Name, Line Break		Dong, wang Dan Xiachan He
9​	9​	TAMKOO LO	Tam Koo, Lo	Name padded		JU YUPER, QO
10​	10​	LI, MARYME	Marymeli	Name Reverse+Name padded, comma		JU-YEAH-AO
11​	11​	Zoo, catup_SHAN	Zoo shan catup	underscore, comma		Mary, Co Yu Yeah
12​	12​	CO-JU YEAH	CO JU-YEAH	Duplicated first name		Marymeli
13​	13​	CO JU Mary YEAH	Mary, Co Yu Yeah	Duplicated first name with one name		Playu, So_Ju
14​	14​	CO JU SUPER	CO JUSUPER	Duplicated first name with name padded		Roger, CO_JU-SUPER
15​	15​	CO JU SUPER, ROGER	Roger, CO_JU-SUPER	Duplicated first name with name padded+Name Reverse		SO JU YEAH
16​	16​	SO JU YEAH	SO JU YEAH	Duplicated Last 2 name(from No.12)		SO JU_YUPER
17​	17​	SO JU YUPER	SO JU_YUPER	Duplicated first name with last name different		Tam Koo, Lo
18​	18​	SO JU PLAY U	Playu, So_Ju	Duplicated first name with last name different+Name Reverse+padded		THAN COO
19​	19​	QO YUPER JU	JU YUPER, QO	Duplicated Last name with surname name different		Xa Yoo Jbo, Lee
20​	20​	AO JU YEAH!	JU-YEAH-AO	Duplicated Last name with last name different+Name Reverse+padded		Zoo shan catup
21​
22​		Given Blue, I want to return Red!!!!!

<tbody>
</tbody>

 

[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?