Unique values between 2 tables

mwlenzac

New Member
Joined
Feb 20, 2019
Messages
4
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
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,722
Try this macro:
Code:
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
 
Last edited:

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,128
Messages
5,628,864
Members
416,346
Latest member
Sekolaine

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top