MATCH 2 column with diffrent name format

[santoshsapre]

Dear Narayan,


not hear from u..

 

[NARAYANK991]

Hi Santosh ,


Luke has already given you the macro ; have you tried it out and compared the results you get with the results you got earlier with the formulas ?


I did not reply since Luke had already posted the macro you were looking for.


Narayan

 

[Elbillionz]

Hi oldchippy ,


I'd like to submit the following 'slightly' smaller formula :


=IFERROR(IF(LEN(A3)-LEN(SUBSTITUTE(A3," ",""))=0,A3=B3,IF(LEN(A3)-LEN(SUBSTITUTE(A3," ",""))=1,AND(SEARCH(LEFT(A3,FIND(" ",A3)-1),B3)>0,SEARCH(RIGHT(A3,LEN(A3)-SEARCH(" ",A3)-1),B3)>0),IF(LEN(B3)-LEN(SUBSTITUTE(B3," ",""))=0,SEARCH(RIGHT(B3,LEN(B3)-SEARCH(" ",B3)-1),A3)>0,IF(LEN(B3)-LEN(SUBSTITUTE(B3," ",""))=1,AND(SEARCH(LEFT(B3,SEARCH(" ",B3)-1),A3)>0,SEARCH(RIGHT(B3,LEN(B3)-SEARCH(" ",B3)-1),A3)>0),SUM(IFERROR(SEARCH(LEFT(A3,SEARCH(" ",A3)-1),B3)>0,0),IFERROR(SEARCH(MID(A3,SEARCH(" ",A3)+1,SEARCH(" ",A3,SEARCH(" ",A3)+1)-SEARCH(" ",A3)-1),B3)>0,0),IFERROR(SEARCH(RIGHT(A3,LEN(A3)-SEARCH(" ",A3,SEARCH(" ",A3)+1)),B3)>0,0))>1)))),FALSE)


At least on the few examples that have been given , it seems to work correctly.


Basically what is being done is :


1. Check the number of spaces between the words in cell A3.


2. If the number of spaces = 0 , then the two cells A3 and B3 have to match , so check A3=B3.


3. If the number of spaces = 1 , then check if the two words in A3 occur in B3 , by separating the words in A3 and using the SEARCH function ; SEARCH is used because it is case insensitive.


4. If the number of spaces in A3 = 2 , it means there are 3 words in A3 ; check if there are 2 words in B3 ; if yes , then separate the words in B3 and match them in A3.


5. If the number of words in both A3 and B3 is 3 , then see if at least 2 words in A3 match those in B3.


Narayan


P.S. : I am sure this can also be improved upon.

Hi Santosh ,


You can try out this formula , where a check to see if any "word" is more than 1 character long ( to exclude matching initials ) has been incorporated. Again , only checking it out with a lot of data will "prove" that it is sufficient for your purposes.


=IFERROR(IF(LEN(A3)-LEN(SUBSTITUTE(A3," ",""))=0,A3=B3,

IF(LEN(A3)-LEN(SUBSTITUTE(A3," ",""))=1,

AND(SEARCH(LEFT(A3,FIND(" ",A3)-1),B3)>0,SEARCH(RIGHT(A3,LEN(A3)-SEARCH(" ",A3)-1),B3)>0,LEN(LEFT(A3,FIND(" ",A3)-1))>1,LEN(RIGHT(A3,LEN(A3)-SEARCH(" ",A3)-1))>1),

IF(LEN(B3)-LEN(SUBSTITUTE(B3," ",""))=1,

AND(SEARCH(LEFT(B3,SEARCH(" ",B3)-1),A3)>0,SEARCH(RIGHT(B3,LEN(B3)-SEARCH(" ",B3)-1),A3)>0,LEN(LEFT(B3,FIND(" ",B3)-1))>1,LEN(RIGHT(B3,LEN(B3)-SEARCH(" ",B3)-1))>1),

AND(SUM(IFERROR(AND(LEN(LEFT(A3,SEARCH(" ",A3)-1))>1,SEARCH(LEFT(A3,SEARCH(" ",A3)-1),B3)>0),0),

IFERROR(AND(LEN(MID(A3,SEARCH(" ",A3)+1,SEARCH(" ",A3,SEARCH(" ",A3)+1)-SEARCH(" ",A3)-1))>1,SEARCH(MID(A3,SEARCH(" ",A3)+1,SEARCH(" ",A3,SEARCH(" ",A3)+1)-SEARCH(" ",A3)-1),B3)>0),0),

IFERROR(AND(SEARCH(RIGHT(A3,LEN(A3)-SEARCH(" ",A3,SEARCH(" ",A3)+1)),B3)>0,LEN(RIGHT(A3,LEN(A3)-SEARCH(" ",A3,SEARCH(" ",A3)+1)))>1),0))>1)))),FALSE)


Narayan

Hi
NARAYANK991
Excel Ninja,
i was looking for solutions to a similar challenge when i stumbled on this post on google and its the closest to offering a solution to the challenge i have.
Please what is the purpose of "&#62" in the above formular. Ive tried to unravel it to no avail with my little sense of excel.

 

[vletm]

Elbillionz
it's > based

HTML 4.0 Special Entities

A table of the HTML 4 entities for markup-significant and internationalization characters.

www.htmlhelp.com

and
Please, next time, You should open a new tread as
written in Forum Rules

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

which You've just read.