Comparing Paragraphs

[clmarcela]

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

[ashleyuk1984]

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.

[clmarcela]

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

[snb]

Crossposted:

http://www.mrexcel.com/forum/excel-questions/822141-compare-paragraphs.html

[ashleyuk1984]

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



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

[clmarcela]

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

[clmarcela]

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 ?




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