#### santoshsapre

##### New Member

Dear Narayan,

not hear from u..

not hear from u..

- Thread starter santoshsapre
- Start date

Dear Narayan,

not hear from u..

not hear from u..

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

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.

HiHi 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

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 ">" in the above formular. Ive tried to unravel it to no avail with my little sense of excel.

it's

`>`

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

www.htmlhelp.com

Please, next time, You should open a new tread as

written in Forum Rules

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