Compare similarity between two columns

[Shahks]

Hi all,

I am currently trying to compare similarities between columns A and B. Both columns have names of companies and I want to check whether one is a subsidiary or sister company of another. And I would like column C to provide the desired results as shown below.

I have highlighted the similarities in red that the results would be based upon. Could anyone please help with a formula that could perform this analysis?

Thanks and regards!

[#2]

@Shahks You could try the following formula-

=OR(ISNUMBER(XMATCH("*"&TEXTSPLIT(A2,," ")&"*",B2,2)))

Few questions, how excel will know what is your company name (which part of string)? Above formula will return false positive if both column has same company type like LLC, Ltd, Gmbh? Suppose you have xyz LLC and abc LLC. In this case the formula will return TRUE.

[#3]

@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)