New Contributor

# Compare similarity between two columns

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 Replies

# Re: Compare similarity between two columns

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

# Re: Compare similarity between two columns

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

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.