PDA

View Full Version : Solved: Count words equal between 2 cells



MDY
09-27-2011, 07:12 PM
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.

MDY
09-27-2011, 09:06 PM
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

MDY
09-27-2011, 09:49 PM
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