Find out the length of common string between cells of two columns.

[rampulaparthi]

Hi all ,

I have a problem where I need to find out the common intersection string between two cells of adjacent columns and find its length .

something like F2 = length ("D2" intersection "E2")

D2 = Tommy Hilfiger Bohemian Dress
E2 = Tommy Hilfiger Hawaaian Dress

D2 intersection E2 will be Tommy Hilfiger Dress

the column F2 should be filled with value of 19 which is length of intersection string


I want to do this for whole columns D and E.

Thanks in advance.

[Andy Pope]

You need to provide more information on what exactly defines an intersection.

D2 = Tommy Hilfiger Red Dress
E2 = Tommy Hilfiger Green Dress

Would the above still result in 19?

[JasperD]

Try this :

Please Login or Register  to view this content.

Please remember to click the * below if this helps

[rampulaparthi]

Thanks Jasper .. I will try testing it now

[rampulaparthi]

You need to provide more information on what exactly defines an intersection.

D2 = Tommy Hilfiger Red Dress
E2 = Tommy Hilfiger Green Dress

Would the above still result in 19?

Yes Andy technically it should just return the length of common words . Its okay if we ignore the spaces .. My goal is to find out how different is the string in column E from column D .. so then il again compare the common string result with the string in column D and then analyze

[Andy Pope]

I still think your spec is too vauge.

D2 = Tommy Hilfiger Dress Red
E2 = Tommy Hilfiger Green Dress

Does this still return 19?

So is this just a duplicate word count function, or is the order of the words important?

[rampulaparthi]

Okay let me explain the problem clearly .

Column D has string that is copied into column E and few changes are done on that string. Technically both the strings will be closely similar . Due to manually copying and pasting information in column E , there has been few row mis-matches so I want to find out which rows have been mis matches . For that I though comparing the string length of column D with that of the intersection length and sorting that comparison number should give me a fair idea where the mis matches are . I will devise a way to find mis matches.

I am adding sample data


data.xlsx

Thanks in advance

[Andy Pope]

why not simply use =D2=E2 and then filter on FALSE

[rampulaparthi]

No because they wont be technically equal .. the data in E2 is altered a bit .. not very much but slightly. Most of the string would be similar but not totally equal .

[rampulaparthi]

@jasper

Thanks a lot your code works just fine ...and yes clicked the star

[Andy Pope]

this array formula will tell you when the text goes out of sync

=MIN(IF(MID(D2,ROW(1:100),1)<>MID(E2,ROW(1:100),1),ROW(1:100),999))

[rampulaparthi]

Try this :

Please Login or Register  to view this content.

Please remember to click the * below if this helps

Hi jasper I have tested this code. Few issues i found out were The intersection of strings was not being calculated if both of them started with different words .

example "One good man was there" and "A good man was there" the result must be 15 excluding the count of spaces . But this code gives me result of 0 .

It would be great if this can be fixed some how ! Thanks in advance

[JasperD]

Ok, let me alter that (it's not impossible, just a b*tch).

What outcome do you expect on :
"One good man was there" vs "A good man was there yesterday"

and on :
"Apple, Banana, Orange, Pear" vs "Cat, Fish, Banana, Dog"

Thanks

[jindon]

Try this

Please Login or Register  to view this content.

[rampulaparthi]

@Jasper

"One good man was there" vs "A good man was there yesterday" Expected result = len(good man was there) = 18

"Apple, Banana, Orange, Pear" vs "Cat, Fish, Banana, Dog" Expected result = len(Banana) = 6

[rampulaparthi]

Try this

Please Login or Register  to view this content.

Thanks Jindon it works but then i run into Run-time error '5018':
Application-defined or object-defined error at line
"For Each m In .Execute(r(, 2).Value)"

and the code execution stopped . One reason i identified it happened was because of + sign in the D column I eliminated all the + signs and when "(" is present in the D column works fine if i have "(" and ")" in the same cell but then again the code gave an error for the string

Got Gray? Bodysuit Vs Idakoos Unisex-baby "Got Gray?" Bodysuit

Can you fix this problem ? I will attach my sample datasample data.xlsx

[jindon]

rampulaparthi

Change to

Please Login or Register  to view this content.

[rampulaparthi]

Thanks a lot Jindon !! you helped me a lot !!

[rampulaparthi]

@jindon

Sorry for bothering you so much but theres a problem when i test this code ... I have attached sample data .. its giving significant count even when theres no matching between strings

Error sample data.xlsx

This problem is arising only after the modification you made .. else it was working fine ...

Before this line was added

.Pattern = "([\$\(\)\-\^\|\\\[\]\*\+\?\.\?])"

Thanks again for being patient with my questions

[jindon]

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.