Consulting

Results 1 to 7 of 7

Thread: Comparing Paragraphs

  1. #1

    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

  2. #2
    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.

  3. #3

    Comparing Paragraphs

    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
    Attached Files Attached Files

  4. #4

  5. #5
    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

  6. #6
    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

  7. #7

    Comparing Paragraphs

    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
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •