Comparing Paragraphs

Hello,
Please I need your help.


I have a column with different paragraphs/sentences in each cell, I have to compare them and identify which of them are related to the same topic. Do you know if that is possible ? If that is possible, How can I do it ? If that is not possible by using VBA, Do you know how I can do it ?


Best regards

Well comparing data in Excel is fairly straight forward. However, it would help if you could provide us with a sample workbook, and fully explain what you require. That way we can help you the best we can.

Dear ashleyuk1984,

Thank you for your response.

As you can see in the attached file, the matching descriptions are:

Descriptions number 1 and 3 are related to nuclear information.
Descriptions number 2 and 5 are related to strikes information.
Description number 4 is not similar or does not match with the other descriptions.

I need a macro or function that help me to identify automatically the matching descriptions according to the text content.

Best regards

Crossposted:

http://www.mrexcel.com/forum/excel-q...aragraphs.html

You could use InStr.
Here's something to get you started.

Code:

---

Sub Nuke()


For x = 2 To Range("B" & Rows.Count).End(xlUp).Row
    If InStr(1, Range("B" & x).Value, "nuclear", vbTextCompare) Then
        Range("C" & x).Value = "Nuclear"
        GoTo Nextx
    End If
    If InStr(1, Range("B" & x).Value, "strike", vbTextCompare) Then
        Range("C" & x).Value = "Strike"
        GoTo Nextx
    End If
Nextx:
Next x
   
End Sub

---

Dear ashleyuk1984,

Thank you for your response.

I will try to work with the macro you have provided me. I guess the best approach to identify the similar descriptions is compare word by word in each paragraphs and high light the similar ones. Do you know how I can do this ?

Best regards

Dear ashleyuk1984,

I have found this code, It highlight the text which is not common in the paragraphs but not the text that is common. Do you know how I should modify the code in order to highlight the common text in between the two paragraphs ?

Code:

---

Sub Compare()
If StringCompareHighlight(Range("m1"), Range("o1")) Then
      Range("q1").Value = "Match"
Else
      Range("q1").Value = "Not Match"
End If


If StringCompareHighlight(Range("m2"), Range("o2")) Then
      Range("q2").Value = "Match"
Else
      Range("q2").Value = "Not Match"
End If


If StringCompareHighlight(Range("m3"), Range("o3")) Then
      Range("q3").Value = "Match"
Else
      Range("q3").Value = "Not Match"
End If

End Sub

Function StringCompareHighlight(r1 As Range, r2 As Range) As Boolean
''this function compare the words from 2 strings
''each word is separated by "," and the order of these words does not matter
''return true if matches, False if not match
''changes the format of a word in one string that does not exist in the other string
Dim oMatches As Object, oMatch As Object
Dim r(1 To 2) As Range
Dim i As Integer, bDiff As Boolean, iStart As Integer
 
Set r(1) = r1
Set r(2) = r2
With CreateObject("vbscript.regexp")
    .Pattern = " *(\w+) *(?= .*\|)(?!.*\|.* *\1 *)"
    .Global = True
    .IgnoreCase = True
 
    For i = 1 To 2
        Set oMatches = .Execute(" " & r(i).Text & ",|," & r(3 - i).Text & " ")
        For Each oMatch In oMatches
            iStart = InStr(oMatch.FirstIndex + 1, r(i).Text, oMatch.submatches(0), vbTextCompare)
            With r(i).Characters(Start:=iStart, Length:=Len(oMatch.submatches(0))).Font
                .Bold = True
                .Size = 14
            End With
        Next oMatch
        If oMatches.Count > 0 Then bDiff = True
    Next i
End With
StringCompareHighlight = Not bDiff End Function

---

Best regards