CODEWITHSUNDEEP
excelif-statementformulavba
RTrain3k
RTrain3k

Reputation: 867

IF two cells have at least one common word, true, else false: Excel formula

I have have two columns populated with text. I want to compare row-wise for any identical words between the two cells. How can this be accomplished with an Excel formula or vba function?

Best regards,

Upvotes: 0

Views: 1958

Answers (2)

Sgdva
Sgdva

Reputation: 2800

A third column would be needed. IE:
A..........B..........C
Text,1,another...Text,2,another......'=CommonWords(A1,B1,",") (Result another,Text)
In order to be able to use the UDF paste the following: 

Function CommonWords(Text1 As Variant, Text2 As Variant, Character As Variant)
Dim ArrayText1 As Variant: ArrayText1 = Split(Text1, Character)
Dim ItemArrayText1 As Variant
Dim ArrayText2 As Variant: ArrayText2 = Split(Text2, Character)
Dim ItemArrayText2 As Variant
Dim SummaryCommonWords As Variant
    For Each ItemArrayText1 In ArrayText1
    If InStr(Text2, ItemArrayText1) > 0 And InStr(SummaryCommonWords, ItemArrayText1) = 0 Then SummaryCommonWords = IIf(SummaryCommonWords = "", ItemArrayText1, ItemArrayText1 & Character & SummaryCommonWords)
    Next ItemArrayText1
    For Each ItemArrayText2 In ArrayText2
    If InStr(Text1, ItemArrayText2) > 0 And InStr(SummaryCommonWords, ItemArrayText2) = 0 Then SummaryCommonWords = IIf(SummaryCommonWords = "", ItemArrayText2, ItemArrayText2 & Character & SummaryCommonWords)
    Next ItemArrayText2
    CommonWords = IIf(CStr(SummaryCommonWords) <> "", SummaryCommonWords, "No common words!")
End Function

As an OT:

  1. Wouldn't be better to know which words are repeated to analyze instead of a true, false statement?
    enter image description here
  2. You would need to work it to ignore spaces in the words, caps if needed.

Upvotes: 0

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

Try the following UDF():

Public Function Kompare(s1 As String, s2 As String) As Boolean
    ary = Split(s1, " ")
    bry = Split(s2, " ")
    Kompare = False
    For Each a In ary
        For Each b In bry
            If a = b Then
                Kompare = True
                Exit Function
            End If
        Next b
    Next a
End Function

enter image description here

Upvotes: 1

Related Questions