Compare two lists; return only unique records

  • Thread starter Thread starter Lkyred19
  • Start date Start date
L

Lkyred19

There is data in Column 'A' (514 Records) that will also be in Column 'B'
(1600 Records), I want excel to compare ColA to ColB and remove from B the
duplicate information in A. The end result should Column B without the
duplicate records from A. How do I do this?
 
Public Sub ProcessData()
Dim LastRow As Long
Dim rng As Range

With ActiveSheet

LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
.Range("C1").Resize(LastRow).Formula =
"=IF(ISNUMBER(MATCH(B1,A:A,0)),1,"""")"
.Columns(3).Value = .Columns(3).Value
Set rng =
Range("C:C").SpecialCells(xlCellTypeConstants).Offset(0, -1)
rng.Delete Shift:=xlUp
.Columns(3).ClearContents
End With

End Sub



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Hi,

Let's assume you are using Excel 2003 or earlier:

1. In cell C2 enter the formula COUNTIF(A$2:A$516,B2) copy it down the 1600
rows.
2. Choose Filter, Auto Filter
3. Open the auto filter on column C and choose Custom, Does not equal and
set the value to 0 (zero) This will hide all the rows that are not
duplicates.
4. Select the items in column B and press Delete (if you are using a
version before 2002 let us know). This will remove all the duplicates.
5. Turn off the filters.
6. Highlight column B and sort it. Clear column C.
 
Back
Top