Comparing 2 spreadsheets to remove duplicate names

[Marc Hamel]

I have 2 spreadsheets with exactly the same number of columns. They are
lists of names and addresses of donors to our organization. List 2 is a
smaller list of current donors and list 1 is a larger list of all donors from
the past. I want to send letters to all old donors who are not in list 2.
So I want to compare the 2 lists and eliminate from list 1 those donors who
are in list 2. The first column in each list is a unique Donor number. Can
I do this in Excel without having to buy a third party program? ANY help
would be GREATLY appreciated.

 

[Bernie Deitrick]

Marc,

Open both spreadsheets, and in the sheet with list 1, select a blank cell on the same row as your
first donor, and type

=MATCH(

then click on the cell with the unique donor number, then type a comma, and then navigate to list 2
(using Window / click on the workbook name) and select the column Letter (above row 1) of the column
with the unique donor numbers, and then type , false) and press enter.

Then copy that cell down for as many rows as you have in your list.

Then select your entrie list including the column with the formulas, and sort the list based on the
column with the formulas. Then select all the cells where that formula that returns a number (which
means that the donor number is in BOTH lists) and delete those rows. The remaining list will be
those old donors who were not in list 2.

HTH,
Bernie
MS Excel MVP

 

[Jim May]

In a Blank Column of Your Larger Sheet enter something like this into the top
cell.
Then Edit the Sheet and Cell References which at present assum your smaller
sheet
is Sheet2 of the same Workbook and your listing runs only 3 rows.

=IF(NOT(ISNA(VLOOKUP(A1,Sheet2!$A$1:$A$3,1,FALSE))),"Send Letter","")

After performing the edit Copy down as far as is needed.
Then AutoFilter on Send Letter

HTH
Jim