Hello, I have 2 lists of names/addresses. I have a master list of about 35,000(list1) and another list of about 16,000(list2) that was compiled from names of list1. All I am trying to do is subtract list2 from list1 so that I end up with the 19,000 names that are unique to list1 but not on list 2(list1-list2). I've tried different "advanced filter" methods but can't seem to get it right.

Quote:

---

Originally Posted by RayGary274

Hello, I have 2 lists of names/addresses. I have a master list of about 35,000(list1) and another list of about 16,000(list2) that was compiled from names of list1. All I am trying to do is subtract list2 from list1 so that I end up with the 19,000 names that are unique to list1 but not on list 2(list1-list2). I've tried different "advanced filter" methods but can't seem to get it right.

---

Back up your lists then add a column to each list and compare them using the Match function =MATCH(G2,A$2:A$24,0)

Then you can use this information to merge them as required.

It may not be as elegant as you hoped but it might do the job.

Be well

Put the long list in column A, the short list in column B, then run this macro to get the new names in column C. It is expected that there are titles in A1 an B1 so the first name to check is in row 2.

Code:

---

Option Explicit
Sub ListNewItemsInColumnC()
'Compare long list in A to short list in B, list new items in C
Dim lastrowA As Long
Application.ScreenUpdating = False

lastrowA = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

Columns(3).ClearContents
Range("C1").Value = "New Names"

Range("C2:C" & lastrowA).FormulaR1C1 = "=IF(COUNTIF(C[-1],RC[-2])=0,RC[-2],"""")"
Range("C2:C" & lastrowA).Value = Range("C2:C" & lastrowA).Value
Range("C2:C" & lastrowA).SpecialCells(xlCellTypeBlanks).Delete (xlShiftUp)

Application.ScreenUpdating = True
End Sub

---