Compare similarity between two columns

Shahks 

To automatically recognize and highlight the words, we can use a VBA code.

Follow these steps:

Press the "ALT" + "F11" keys to open the VBA editor.

Click "Insert" and then click "ThisWorkbook" to paste the following VBA code:

Private Sub Workbook_Open()
    Dim lastRow As Long
    Dim i As Long
    Dim words As Variant
    Dim cellA As Range
    Dim cellB As Range
    
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    For i = 2 To lastRow
        Set cellA = Range("A" & i)
        Set cellB = Range("B" & i)
        
        words = Split(cellA.Value, " ")
        
        For Each word In words
            If InStr(1, cellB.Value, word) > 0 Then
                cellA.Characters(InStr(1, cellA.Value, word), Len(word)).Font.Color = RGB(255, 0, 0)
                cellB.Characters(InStr(1, cellB.Value, word), Len(word)).Font.Color = RGB(255, 0, 0)
            End If
        Next word
    Next i
End Sub

Close the VBA editor.

Save your workbook in Excel macro-enabled format (.xlsm) and restart the workbook.

The Workbook will now run and compare the words in column A with those in column B.

If a word in both columns matches, it will be highlighted in red in column A.

Please note that you must ensure that the texts in the cells are formatted correctly, i.e. that the words are separated by spaces.

This solution requires the use of VBA and may not be suitable for all users/Office versions.

Sample file is included.

Hope I was able to help you with this info/VBA Code.

NikolinoDE

I know I don't know anything (Socrates)