Compare two excel workbooks (or two worksheets within a single workbook)

Hi,

I have two excel workbooks containing member data which I need to compare and sort.
Each workbook is from a different date (7/15 and 8/1). I have to work with the data file
-Which members to keep (if the member appears on both files)
-Which members to add (if the member appears on the newer file, but not the older file)
-Which members to remove (if the member appears on the old file, but not the new file)

The way I normally handle it is make a new column in the old file and paste "OLD" down every row of this new column. I then copy the entire worksheet to the new file and sort by name. In the new file I add two new columns, one labeled REMOVE and one labeled ADD. I then proceed to sort the first name and last names columns in A-Z format. Finally, I manually go though the document and put "X"s in the add or remove columns based upon the above conditions. To finish the document, I do a final sort of the REMOVE and ADD columns to have the "X"s at the top of the page.

I'm trying to see if there is any better way of doing this. I'm open to anything, macros, add ons, VBA scripts, etc.

Thanks in advance.

~SZ

Welcome to the forum Sergio,

If you use this formula in a spare column on the latest workbook =COUNTIF([Book1]Sheet1!$A$1:$A$400,A1) and copy down you will get a bunch of 1's (when the names exists on both files), and 0's when the name is NOT on the older file.

Just set up a data filter on ZERO, and those are the names to ADD to your list.

If you do the same on the OLDER file - the 0's will be the list of names to delete.

I hope this helps, please let me know!

Regards,

David

Please remember, your request is clearer if you attach a sample workbook.
- Reply, then Click on Go Advanced and click on the Paper Clip. OR scroll down till you see MANAGE
ATTACHMENTS

- If this is helpful, please click on the *Add Reputation button at the bottom.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".