Compare two table columns on different worksheets and output differences in both to another worksheet

[mrpavlos]

Hello everybody.

Thank you again for such a great forum. I am wondering if somebody could please help with a VBA solution I am stuck on.
The scenario is this:

I have a workbook with three worksheets. On Sheet 1 in column A I have a single column Table populated with data (can be either numbers or letters or combos of both), and I also have a single column Table in column A on Sheet2 populated with data. The data in both Table columns is in random order and the columns will be of differing lengths but with the respective Tables fitting the size of each of the data sets (i.e there are no empty rows). I would like to firstly output a list of those values or cell contents which are in column A of Sheet 1 but which are not in the table on Sheet 2 to an empty Table on Sheet3 called, for example's sake, OutputA, and also output a list of those values or cell contents which are in the Table in Sheet2, but which are not in the Table of Sheet1 to another empty Table called OutputB on Sheet3. If possible too, in cells G2 and G3 respectively on Sheet3 I would like to have a numerical count of those two sets of differences.

Currently I am using a VBA Countif solution, which does work for me, but because the data sets may be very large I am thinking of a dictionary solution but I am not sure how to write it properly especially where I am using listobjects rather than normal ranges.

Many thanks for any help given. Paul

 

[onlyadrafter]

Hello,

not sure if this helps, but one way

Sub FIND_DIFFERENT()
    Application.ScreenUpdating = False
    With Sheets("Sheet1")
        .Range("B2").Formula = "=MATCH(A2,Sheet2!A:A,0)"
        .Range("B2").Copy
        .Range("B3:B" & .Range("A" & Rows.Count).End(xlUp).Row).PasteSpecial (xlPasteAll)
    
        .Range("A1:B1").Select
        Selection.AutoFilter
        Selection.AutoFilter Field:=2, Criteria1:="#N/A"
    
        .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
        With Sheets("Sheet3")
            .Range("A" & .Range("A" & .Rows.Count).End(xlUp).Row).Offset(1, 0).PasteSpecial (xlPasteAll)
        End With
        With Sheets("Sheet1")
            Selection.AutoFilter
            .Columns("B:B").ClearContents
        End With
    End With
    
    With Sheets("Sheet2")
        .Select
        .Range("B2").Formula = "=MATCH(A2,Sheet1!A:A,0)"
        .Range("B2").Copy
        .Range("B3:B" & .Range("A" & Rows.Count).End(xlUp).Row).PasteSpecial (xlPasteAll)
    
        .Range("A1:B1").Select
        Selection.AutoFilter
        Selection.AutoFilter Field:=2, Criteria1:="#N/A"
    
        .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
        With Sheets("Sheet3")
            .Range("B" & .Range("B" & .Rows.Count).End(xlUp).Row).Offset(1, 0).PasteSpecial (xlPasteAll)
        End With
        With Sheets("Sheet2")
            Selection.AutoFilter
            .Columns("B:B").ClearContents
        End With
    End With
    
    Application.ScreenUpdating = True
End Sub

then in G2 add this formula: =COUNTA(A:A) and in G3: =COUNTA(B:B)

 

[kvsrinivasamurthy]

VBA code

Sub nn()
Dim Ary
Dim T As Long, LR1 As Long, LR2 As Long

LR1 = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
LR2 = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row

'Availabe in Sheet1 not in Sheet2

Ary = Filter(Evaluate("Transpose(IF(Countif('Sheet2'!a2:a" & LR2 & ",'Sheet1'!A2:A" & LR1 & ")=0,Row(A2:A" & LR1 & "),False))"), False, 0)
k1 = UBound(Ary)
For T = 0 To k1
Sheets("Sheet3").Range("A" & T + 2) = Sheets("Sheet1").Range("A" & Ary(T))
Next T

'Availabe in Sheet2 not in Sheet1

Ary = Filter(Evaluate("Transpose(IF(Countif('Sheet1'!A2:A" & LR1 & ",'Sheet2'!a2:a" & LR2 & ")=0,Row(A2:A" & LR2 & "),False))"), False, 0)
k2 = UBound(Ary)
For T = 0 To k2
Sheets("Sheet3").Range("B" & T + 2) = Sheets("Sheet2").Range("A" & Ary(T))
Next T

Sheets("Sheet3").Range("G2") = Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Row - 1
Sheets("Sheet3").Range("G3") = Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row - 1

End Sub

 

[mrpavlos]

Hello onlyadrafter and kvsrinivasamurthy. My apologies for the delay in replying. I was caught up with work and couldnt get to the forum. Thank you so much, the both of you, for taking the time to respond and offer a solution to my question. Both solutions work for me. Thanks again.