Unique values between 2 tables

[mwlenzac]

Hi
I have to different tables of data let's say one is on sheet 2, I have the other table on sheet 3.
The 2nd column in each of the two tables is a unique identifier, there will be no duplicates of that data within the same table at all.

What I want to do is run a report on sheet 1 (which is blank) that only shows the the rows of data from table on sheet 2, where the value in the 2nd column does not exists in the 2nd column of sheet 3.

The lengths of the table will differ, because users paste in the two tables of data.

I hope this makes sense in the way I have explained it, but if it has not, please let me know and I will try to elaborate further.
I know I can run a formula that gives a "YES" if it exists and no if it doesn't, but I prefer to not have to do that and then filter it out etc.

Happy to use formulas and/or macros to achieve the result

 

[John_w]

Try this macro:

Public Sub Create_Report()

    Dim destCell As Range
    Dim table1 As ListObject, table2 As ListObject
    Dim r As Long, n As Long
    Dim found As Variant
    
    With ActiveWorkbook.Worksheets("Sheet1")
        .Cells.Clear
        Set destCell = .Range("A1")
    End With
    
    Set table1 = ActiveWorkbook.Worksheets("Sheet2").ListObjects(1)
    Set table2 = ActiveWorkbook.Worksheets("Sheet3").ListObjects(1)
    
    n = 0
    For r = 1 To table1.DataBodyRange.Rows.Count
        found = Application.Match(table1.DataBodyRange(r, 2).Value, table2.DataBodyRange.Columns(2), 0)
        If IsError(found) Then
            'This table1 column 2 value not found in table2 column 2
            If n = 0 Then
                table1.HeaderRowRange.EntireRow.Copy destCell
                n = n + 1
            End If
            table1.DataBodyRange(r, 2).EntireRow.Copy destCell.Offset(n)
            'table1.ListRows(r).Range.Copy destCell.Offset(n)   'alternative - just table columns on row, not entire row
            n = n + 1
        End If
    Next
        
End Sub

 

[Special-K99]

Is there a way to solve this (in one go) using the SMALL/AGGREGATE formula (the answer thats usually given when someone wants to remove blanks from a list) ?
I've been trying to solve this with the SMALL/AGGREGATE method but to no avail.