Hi,
just wondering if anyone can help me out?
I'm after an excel function that can count the number of words which are equal between 2 cells.
I.e.
In A1 Text is: Number of Words Equal
In B1 Text is: Words Equal
In C3 Calculation is: 2
So the calculation in C3 shows that there are 2 equal words between the 2 columns.
Hopefully this is not too difficult!
Thanks in advance for any help!
MDY
mikerickson
09-27-2011, 08:14 PM
You could do this with names.
Select C3 and define these names
Name: lenA
RefersTo: =LEN(Sheet1!$A1)-LEN(SUBSTITUTE(Sheet1!$A1," ",""))+1
Name: oneToA
RefersTo: =ROW(INDEX(Sheet1!$A$1,1,1):INDEX(Sheet1!$A:$A,lenA,1))
Name: wordsA
RefersTo: =TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(Sheet1!$A1," ",REPT(" ",255),oneToA),255))," ",REPT(" ",255)),255))
Name: lenB
RefersTo: =LEN(Sheet1!$B1)-LEN(SUBSTITUTE(Sheet1!$B1," ",""))+1
Name: oneToB
RefersTo: =COLUMN(INDEX(Sheet1!3:3,1,1):INDEX(Sheet1!3:3,1,lenB))
Name: wordsB
RefersTo: =TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(Sheet1!$B1," ",REPT(" ",255),oneToB),255))," ",REPT(" ",255)),255))
Then the formula =SUMPRODUCT(--(wordsA=wordsB)) will return the number of matching words.
If words are duplicated in a cell, they are counted twice.
"dog cat dog" vs. "dog fish" will return 2.
Hi mikerickson (http://www.vbaexpress.com/forum/member.php?u=10706),
This all looks great! But i'm sorry I'm a bit confused....I know its a bit of a hassle but any chance you could put it into an excel attachment?
Thanks a lot!
Mdy
Hi mikerickson (http://www.vbaexpress.com/forum/member.php?u=10706),
Sorry I've worked it out now! Thanks a lot for your solution it works great!!!
Sorry I didn't realise you were simply saying to use the Name Define excel function.
Cheers
Mdy
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.