Compare two rows of numbers. Remove the duplicates

This macro will do it... assuming:

List1 is column A
List2 is column B
Columns D and H are empty and can be used

Option Explicit

Sub ExtractUniques()
Dim LR As Long

LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("H1:H" & LR)
    .FormulaR1C1 = "=MATCH(RC1, C2, 0)"
    .SpecialCells(xlFormulas, 16).Offset(, -7).Copy Range("D1")
    .ClearContents
End With

LR = Range("B" & Rows.Count).End(xlUp).Row
With Range("H1:H" & LR)
    .FormulaR1C1 = "=MATCH(RC2, C1, 0)"
    .SpecialCells(xlFormulas, xlErrors).Offset(, -6).Copy Range("D" & Rows.Count).End(xlUp).Offset(1)
    .ClearContents
End With

Range("D:D").Columns.AutoFit
End Sub

How/Where to install the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.

Hello and welcome to the forum,

There are several ways to accomplish this, but there are several factors to determine the best opiton for you.

1) How long are your real lists?
2) Is there any associated data on the same column that will also be deleted or is the whole data one column?
3) Is this a one-time issue or will you be repeating this on a regular occasion?

JB's solution should work well in this situation.

Hi

Another option not using VBA,is this.

Name your Lists as List1 & List2

Then in C2(C1, is the title or empty), use this Array formula(Control+Shift+Enter) not just Enter and copy down,

=IFERROR(IFERROR(INDEX(List1, MATCH(0, COUNTIF($C$1:C1, List1), 0)), INDEX(List2, MATCH(0, COUNTIF($C$1:C1, List2), 0))), "")

Is this, works for you?

If that takes care of your need, please select Thread Tools from menu above and set this topic to SOLVED.

or use advanced filter!