VBA Compare Tool

[Esteban7784]

Hi,

I would like to use a macro to do the following:
I want to know what the differences are between sheet 1 & sheet 2 and list them up in sheet 3.

Sheets 1 and 2 only have data in 1 column (Column A). The number of filled data should be variable.

can somebody help me with this please?

 

[RickXL]

Hi,

Try this:

Sub SpotDups()
    Dim arr1  As Variant
    Dim arr2  As Variant
    Dim arr3  As Variant
    Dim dic   As Object
    Dim key   As String
    Dim i     As Long
    Dim j     As Long
    
    With ThisWorkbook.Worksheets("Sheet1")
        arr1 = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
    End With
    With ThisWorkbook.Worksheets("Sheet2")
        arr2 = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
    End With
    
    Set dic = CreateObject("Scripting.Dictionary")
    
    For i = 1 To UBound(arr1)
        key = arr1(i, 1)
        dic(key) = 1
    Next
    For i = 1 To UBound(arr2)
        key = arr2(i, 1)
        If dic.exists(key) Then
            If dic(key) = 1 Then dic(key) = 3
        Else
            dic(key) = 2
        End If
    Next
    
    ReDim arr3(1 To dic.Count, 1 To 2)
    For i = 1 To dic.Count
        If dic.items()(i - 1) <> 3 Then
            j = j + 1
            arr3(j, 1) = dic.keys()(i - 1)
            arr3(j, 2) = dic.items()(i - 1)
        End If
    Next
    
    With ThisWorkbook.Worksheets("Sheet3")
        .Cells.Clear
        .Range("A1").Resize(, 2) = Array("ID", "Code")
        .Range("A2").Resize(UBound(arr3, 1), UBound(arr3, 2)) = arr3
    End With
End Sub

It reads column A from sheet1 into an array and repeats for sheet2.
It then uses a Dictionary object to identify the differences. Every entry from sheet1 is added to the Dictionary and an Item number of 1 is recorded.
Then sheet2 records are processed. If the key is already in Sheet1 then the Item is changed to 3 otherwise it is set to 2. So, Item=1 means code is in sheet1 only; Item=2 means the code is in sheet2, only; Item=3 means the code is in both sheets.

The results are copied to an array for outputting to sheet3. Codes in both sheets are dropped.

Regards,

 

[Esteban7784]

Hi RickXL,

thanks a lot for your help. Everything works as it should be!

Regards,

 

[RickXL]

No problem, thanks for letting me know.

Regards,