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

mrpavlos

New Member
Joined
Jul 28, 2018
Messages
23
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
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

not sure if this helps, but one way

VBA Code:
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

Well-known Member
Joined
Nov 6, 2013
Messages
643
VBA code

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
 
Last edited:

mrpavlos

New Member
Joined
Jul 28, 2018
Messages
23
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,865
Messages
5,542,956
Members
410,579
Latest member
bdubz
Top