Delete entries that match second sheet.

  • Thread starter Thread starter Sam
  • Start date Start date
S

Sam

About 3 times a year I process excel lists(Mail Out List)of several
thousand names from various databases for policial mail outs.

I also have an excel spreadsheet(Delete List)of several hundred names
we always remove - (if they exsist) from any newly created mail out
list.

Both the Last Name and the Address need to match when the delete
list(sheet2) is comared to the mail out list(sheet1) and any matching
entries (entire row) needs to be deleted from sheet1 (Mail Out List)
and the remaining rows moved up.


Sheet1:
A large spreadsheet (49,000+ entries). Headings listed below:

FirstName(A1),LastName(B1),Address(C1),City(D1),State(E1),Zip(F1)

Sheet2:
A second spreadsheet of several hundred entries (same headings as
above) - all these entries need to be removed from sheet1 (if they
exsist).

I've looked - and have found some macros that are close - but haven't
found any yet that look at and compare more than one column of data.

Appreciate any help,
Thanks
Sam
 
Hi Sam
Are you sure that name and address data are completely the same? Mailing
list are notorious for all types of errors to creep in. There are
specialist tools aimed at this market and you might consider one of these
rather than trying to emulate these in Excel.

I have a lot of experience in this area - I use a product called Match-It
but there are others. Try a search on the web for mailing list deduping
tools.

If you really want to use Excel, then you might consider building a match
key by taking elements from each column in each sheet and then using the
keys to look for duplicates. This proces of list matching can be long
winded. Another approach is to combine both lists (appropriately marking
each record with the sheet source). Then sorting the file using the match
key and the sheet source field. Duplicate are now neighbours and it would
be a simple process of running through the list to mark duplicates and then
remove them.

It also sounds like you might run out of space in Excel, so watch the 65K
limit row limit.

Cheers
Nigel
 
Back
Top