Compare and Count similar words in a cell

[xtreme07]

Hi,

Considering we have 2 cells A1 cell and B1 cell
A1 has the folowing group of words: count me in
B1 has the following group of words: me count in

notice there are the same words arranged in different order

What i need is to make the C1 cell (3rd cell) to look into the A1 and take the word "count", then the word "me", then the word "in" and see if any of these appear in the B1 cell. if they do exist then display in the C1 the number 1. else, display 0

i'd appreciate some input on this.

 

[Aladin Akyurek]

High Plains Grifter - very nice; is working great i need tho more flexibility as i'm using thousands of keywords that are 2-8 words long. if this ain't possible hopefully i'll find a way to use your formula as a macro.

JoeMoe - your formula is outputs same results as Aladin's formula and that's that it returns 1 if any of the words from A1 is found in B1.

so, i'm wondering if it's possible to make C1 return 1 if all the substrings in the cell A1 meet the substrings of B1 or viceversa; else retun 0

It looks like your specs are changed. I think it would be better the new task in VBA...

 

[High Plains Grifter]

the vba answers were better than the formulae right from the start, and all this hard work is basically because we are choosing the harder, more complex road. I posted the formulae because I am learning vba and not yet in the habit of going straight to macros for answers, but for flexibility, simplicity and speed, they are certainly better than the formulae. However, if you would like me to separate out 8 words from a space separated string, I will do so after work today. Let me know.

 

[al_b_cnu]

Hi xtreme07, I saw your post on the 'Fuzzy matching' thread, but am answering it here.

Fuzzy matching wont help you in this case, and you have some real excel 'heavyweights' answering your post.

For the record, my solution would be:

Excel Workbook
	A	B	C
2	alpha Beta Gamma	Gamma Alpha Beta	1
3	alpha Beta Gamma	beta ALPHA gamma	1
4	alpha Beta Gamma	alpha Beta gamma delta	1
5	alpha Beta Gamma	alfa beta gamma	0
6	alpha Beta gamma delta	alpha Beta Gamma	0
Sheet1

Excel 2003

Cell Formulas
Range	Formula
C2	=IF(matchwords(A2,B2),1,0)
C3	=IF(matchwords(A3,B3),1,0)
C4	=IF(matchwords(A4,B4),1,0)
C5	=IF(matchwords(A5,B5),1,0)
C6	=IF(matchwords(A6,B6),1,0)

The UDF code is:

Option Explicit

Function MatchWords(ByVal String1 As String, _
                    ByVal String2 As String) As Boolean
Dim baMatch1() As Boolean, baMatch2() As Boolean
Dim iPtr1 As Integer, iPtr2 As Integer
Dim saString1() As String, saString2() As String

saString1 = Split(" " & LCase$(WorksheetFunction.Trim(String1)), " ")
saString2 = Split(" " & LCase$(WorksheetFunction.Trim(String2)), " ")

ReDim baMatch1(1 To UBound(saString1))
ReDim baMatch2(1 To UBound(saString2))

For iPtr1 = 1 To UBound(saString1)
    For iPtr2 = 1 To UBound(saString2)
        If baMatch2(iPtr2) = False Then
            If saString1(iPtr1) = saString2(iPtr2) Then
                baMatch2(iPtr2) = True
                baMatch1(iPtr1) = True
            End If
        End If
    Next iPtr2
Next iPtr1
MatchWords = True
For iPtr1 = 1 To UBound(baMatch1)
    If baMatch1(iPtr1) = False Then
        MatchWords = False
        Exit For
    End If
Next iPtr1
End Function

 

[xtreme07]

Hi xtreme07, I saw your post on the 'Fuzzy matching' thread, but am answering it here.

Fuzzy matching wont help you in this case, and you have some real excel 'heavyweights' answering your post.

For the record, my solution would be:
[/CODE]

al_b_cnu - i'm out of words .. that's how happy i am right now finally i can get into action and analize the thousands of keywords. ty very much for taking the time and helping me out with this. very much appreciated


@

my other two heroes ... ty very much for putting the effort into this and helping me out. very much appreciated.

 

[xtreme07]

Hi xtreme07, I saw your post on the 'Fuzzy matching' thread, but am answering it here.

Fuzzy matching wont help you in this case, and you have some real excel 'heavyweights' answering your post.

For the record, my solution would be:
[/CODE]

al_b_cnu - i'm out of words .. that's how happy i am right now finally i can get into action and analize the thousands of keywords. ty very much for taking the time and helping me out with this. very much appreciated


@
High Plains Grifter
Aladin Akyurek

my other two heroes ... ty very much for putting the effort into this and helping me out. very much appreciated.

 

[al_b_cnu]

Actually there's a small bug

There should be an 'Exit For' statement after 'baMatch1(iPtr1) = True'

The amended UDF should be:

Option Explicit

Function MatchWords(ByVal String1 As String, _
                    ByVal String2 As String) As Boolean
Dim baMatch1() As Boolean, baMatch2() As Boolean
Dim iPtr1 As Integer, iPtr2 As Integer
Dim saString1() As String, saString2() As String

saString1 = Split(" " & LCase$(WorksheetFunction.Trim(String1)), " ")
saString2 = Split(" " & LCase$(WorksheetFunction.Trim(String2)), " ")

ReDim baMatch1(1 To UBound(saString1))
ReDim baMatch2(1 To UBound(saString2))

For iPtr1 = 1 To UBound(saString1)
    For iPtr2 = 1 To UBound(saString2)
        If baMatch2(iPtr2) = False Then
            If saString1(iPtr1) = saString2(iPtr2) Then
                baMatch2(iPtr2) = True
                baMatch1(iPtr1) = True
                Exit For
            End If
        End If
    Next iPtr2
Next iPtr1
MatchWords = True
For iPtr1 = 1 To UBound(baMatch1)
    If baMatch1(iPtr1) = False Then
        MatchWords = False
        Exit For
    End If
Next iPtr1
End Function

 

[xtreme07]

Actually there's a small bug

There should be an 'Exit For' statement after 'baMatch1(iPtr1) = True'

The amended UDF should be:

ty for the update.
after testing your macro on my campaign i've realized i also need the words from column B2 without spaces, to be recognized as the ones with space.

example:
group of words without space: onetwo
group of words with space: one two

below is the table example

Excel Workbook
	A	B	C
2	one two three	one two three four	1
3	one two three	onetwothreefour	1
Sheet1

Excel 2003

wondering if it's possible to add this functionality to the macro that is already build.
if it's to much of a hastle than i'm ok without this functionality, tho it would help me very much for my campaigns.

 

[al_b_cnu]

Option Explicit

Function MatchWords(ByVal String1 As String, _
                    ByVal String2 As String) As Boolean
Dim baMatch1() As Boolean, baMatch2() As Boolean
Dim iPtr1 As Integer, iPtr2 As Integer
Dim saString1() As String, sString2 As String

saString1 = Split(" " & LCase$(WorksheetFunction.Trim(String1)), " ")
sString2 = LCase$(WorksheetFunction.Trim(String2))

ReDim baMatch1(1 To UBound(saString1))
For iPtr1 = 1 To UBound(saString1)
    If InStr(sString2, saString1(iPtr1)) <> 0 Then
        sString2 = Replace(sString2, saString1(iPtr1), " ")
        baMatch1(iPtr1) = True
    End If
Next iPtr1

MatchWords = True
For iPtr1 = 1 To UBound(baMatch1)
    If baMatch1(iPtr1) = False Then
        MatchWords = False
        Exit For
    End If
Next iPtr1
End Function