Compare two columns of texts in Excel 2003

[K]

Hi all,

If I have name such as Fidelity Funds - Global Technology Fund in column A
and name Technology Fund in column B. is there a way to present the result
of total number of matching words in column C? In this case, column C would
have 2 because of word Technology and Fund in Column A and B. I need to
apply that method to 8000 records.

Thanks,

K

 

[Gary''s Student]

Here is a very small UDF:

Public Function matchwords(r1 As Range, r2 As Range) As Integer
v1 = r1.Value
v2 = r2.Value
s1 = Split(v1, " ")
s2 = Split(v2, " ")
matchwords = 0
For i = 0 To UBound(s1)
vv1 = s1(i)
For j = 0 To UBound(s2)
vv2 = s2(j)
If vv1 = vv2 Then
matchwords = matchwords + 1
End If
Next
Next
End Function

So if A1 contains:
Now is the time for all


and B1 contains:
time is on our side

the formula:

=matchwords(A1,B1) will display 2

NOTE:

This is only a demo and not a full solution. For example, you need to be
concerned with multiple matches:

the
compared to
the the the

Also if you want upeer/lower case to afffect the match.


UDFs are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To use the UDF from the normal Excel window, just enter it like a normal
Excel Function

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

 

[K]

Thank you very much for your help.

1) Is there a way to count the same word just once?

2) Is there a way to remove all "-" in a string of words and keep all spaces?
ex. aaa-bbb-ccc-ddd after the method would be aaa bbb ccc ddd

3) Is there a way to count how many words in a cell?
ex. today is Tuesday, would have a result of 3

thanks,

K

 

[Gary''s Student]

The answer to all three questions is "yes"

The first question depends on what you want the count to be for specific
values. For example:

the cat and the dog
compared with:
the mouse and the flea and the rabbit

There are two "the"s in the first string and three in the second.
There is one "and" in the first string and two in the second.

What should the function return???

The second question is easy; we can just substitute a space in-place-of a
dash.
The third question does not even need VBA. Assuming a single space between
words, the count of words is the count of spaces plus one:

=LEN(E13)-LEN(SUBSTITUTE(E13," ",""))+1

 

[K]

thanks for your help. I will think about the first question.

 

[K]

if I have "Today is Tuesday" in column A and "Today is Tuesday Today is
Tuesday" in column B. Is it possible to compare column A and B and have a
result like "match" in column C? So kind of like using column A as a phrase
and not looking at each words individually.

thanks,

K

 

[Gary''s Student]

=IF(LEN(B1)=LEN(SUBSTITUTE(B1,A1,"")),"no match","match")

 

[K]

thank you again.

K