Excel – How to compare two columns with other two columns for duplicates in Excel

Using Conditional Formatting

Highlight column A. Click Conditional Formatting > Create New Rule > Use this formula to determine which cells to format > Enter the ff. formula:

=countif($B:$B, $A1)

Click the Format button and change the Font color to something you like.

Repeat the same for column B, except use this formula and try another font color.

=countif($A:$A, $B1)

Using a Separate Column

In column C, enter the ff. formula into the first cell and then copy it down.

=if(countif($B:$B, $A1)<>0, "-", "Not in B")

In column D, enter the ff. formula into the first cell and then copy it down.

=if(countif($A:$A, $B1)<>0, "-", "Not in A")

Both of these should help you visualize which items are missing from the other column.

This VBa does it (how to add VBa). I have provided a few options so you can scale it in the future, check out the first 12 lines or so where you can type in the various 'answers' . You can choose which is the starting row and ending row, where the results will be displayed and what words to show if there is a text match or not! Please note, the highlighting is due to the Excel Doc you provided, and nothing to do with the code.

Before running a VBa script, take a back up of the file - there is usually no undo option!

Sub DoTheThing()

'Answer these questions or ye walk the plank

Dim row As Integer
row = 2

Dim firstColumn As String
firstColumn = "B"

Dim lastColumn As String
lastColumn = "D"

Dim resultsColumn As String
resultsColumn = "G"

Dim isFoundText As String
isFoundText = "YES"

Dim isNotFoundText As String
isNotFoundText = "Good Job"

'***Below be for the cap'ains eyes only.

Do While Range("A" & row).Value <> ""

    Dim startChar As Integer
    startChar = Asc(firstColumn)

    Dim endChar As Integer
    endChar = Asc(lastColumn)

    Dim i As Integer

    Dim hasMatch As Boolean
    hasMatch = False

    For i = startChar To endChar

    If Range(Chr(i) & row).Value = Range(Chr(i + 1) & row).Value Then

        hasMatch = True

    End If

    If Range(Chr(startChar) & row).Value = Range(Chr(i + 1) & row).Value Then

        hasMatch = True

    End If

    Next i

    If (hasMatch) Then
        Range(resultsColumn & row).Value = isFoundText
    Else
        Range(resultsColumn & row).Value = isNotFoundText
    End If

row = row + 1


Loop

End Sub

I wrote the results to Col G (to keep your original as is)

After the vba is run