VBA to Compare Two Columns

[dim4x4]

Hello!

I tried to find a solution to my question in exiting threads, but none of them exactly do what I want. The closest to what I need is this thread: VBA to Compare Columns, but for some reason, the solution there does not work in my case.

I have two columns A and B with names. They have different number of entries, but both over a 1000. I want in column D to list all the names which are present in column A, but absent from column B. In column E, I want to list all the names which are present in column B, but absent from column A. The lists in columns A and B should remain as is.

Thank you!

 

[Zot]

I'm assuming data has Header in A1 and B1

Sub Compare_ListMissing()

Dim n&, eRow1&, eRow2&
Dim NotFound As Boolean
Dim cell1 As Range, cell2 As Range
Dim rng1 As Range, rng2 As Range

Application.ScreenUpdating = False

' Get last row for each data on Sheet1 and Sheet2
eRow1 = Range("A1").End(xlDown).Row
eRow2 = Range("B1").End(xlDown).Row

' Define Range for each sheet
Set rng1 = Range("A2", "A" & eRow1)
Set rng2 = Range("B2", "B" & eRow2)

' Compare list in rng1 to rng2
n = 1
NotFound = True
For Each cell1 In rng1
    For Each cell2 In rng2
        If cell2 = cell1 Then
            NotFound = False
            Exit For
        End If
    Next
    If NotFound Then
        n = n + 1
        Range("D" & n) = cell1.Value2
    End If
    NotFound = True
Next

n = 1
NotFound = True
' Compare list in ws2 to ws1
For Each cell2 In rng2
    For Each cell1 In rng1
        If cell1 = cell2 Then
            NotFound = False
            Exit For
        End If
    Next
    If NotFound Then
        n = n + 1
        Range("E" & n) = cell2.Value2
    End If
    NotFound = True
Next

' Return cursor to cell A1 for each worksheet
Application.Goto Range("A1"), True
       
Application.ScreenUpdating = True

End Sub

 

[dim4x4]

Works great! Thank you very much, Zot!