Link to home
Create AccountLog in
Avatar of jer2eydevil88
jer2eydevil88Flag for United States of America

asked on

How do I compare two excel spreadsheets and remove the entries found on both?

I am using Office 2013 with two Excel spreadsheets that have email addresses on them.  I need to compare sheet 1 to sheet 2 then remove the email addresses that are found on the second sheet.
Avatar of Richard Daneke
Richard Daneke
Flag of United States of America image

If I have the question correct, you are trying to add only the new email addresses from the second worksheet onto the first worksheet?

The steps I am recommending will preserve your original worksheets.

1. Copy the first worksheet into a new workbook.
2. Copy the email addresses from the second worksheet and paste them below the addresses on the list copy created in step 1.   This will add all the email addresses from the second worksheet after the first worksheet.
3. Click on any cell in the list of email addresses.
4. Click on Data to use the Data Ribbon.
5. Click on Remove Duplicates in the Data Tools ribbon group.
6. Click OK - this will remove all duplicated lines in the data.  IF you only want to delete lines where the email address is duplicated, uncheck the other column heading boxes leaving only your email address column selected.  Then, click OK to remove the duplicates.

Feel free to ask additional questions.
Avatar of jer2eydevil88


Quick question about #6: Will this remove both examples of the same email or just the first one it comes accross?  My goal is to eradicate the data entirely not leave a copy of it in place.
This will remove the  first line it comes across.  

If you want to remove the duplicated email addresses , both lines, you need to do the following.  

1. Highlight the column of email addresses.
2. From the Home ribbon, choose Conditional Formatting in the Styles ribbon group.
3. From this menu, select Highlight Cell Rules, then, Duplicate Values.
   This will highlight all duplicated email addresses.
4. Remove the lines with highlighted email addresses
   (If you sort the table by email address, but sort by color, they will all be grouped together)
There are a lot of these, how can I automate the removal?
(If you sort the table by email address, but sort by color, they will all be grouped together)

Click on a cell in your list.
From the Data ribbon, choose Sort.
In Sort By, choose your email address column heading.
In Sort On, Choose Cell Color.
Click OK to Sort.

Highlight the cells with duplicates, right-click in the highlight.
Choose Delete... from the menu and choose Entire Row from the Window.

Your duplicates are now removed.

You could record the steps as a macro if you have to do it often.
Or, one could write a macro to handle this if you have to do it often.
That was not the part of your question.
Alternatively, rather than using Conditional Formatting to highlight use a COUNT in a column alongside the e-mail addresses.

Assuming addreses in column A from row 1 to 10,000, in column B:


Adjust the column and row references accordingly and copy down as far as required. the result will be 1 for single entries or >1 for entries with duplicates.

Apply a filter to this column and hide those with value 1. Then select the remaining rows and Delete. Or sort on this column and delete those not required.

Rob H
In Workbook 1 (my master list) I have a header row that shows the following data:
Last Name      
First Name      
Office Phone      
Office Fax      

While in Workbook 2 (list of emails I need to remove) I only have:

Can you provide instructions that will sort all the columns in Workbook 1 so the data matches up and I can remove the entire line including the duplicate email.
Still not really clear what you are trying to achieve.

You have a sheet with 11 columns, 1 of which is email. Are you trying to identify which of those are also in your second list?
Yes and remove the lines from the first that have those emails.
In your more extensive list copy the e-mail list onto a separate sheet (assuming Sheet2 and columnA) and add a column of formulas checking the existence of the email:


Assumed email in extensive list in column G from list of headers above.

For those that exist in both, this will give the row number from the secondary list or an error for those that don't. You can then filter on this column and delete rows.

If you also want to remove from the secondary list, in column B of the secondary sheet:


Again will return row number or error.

Rob H
I'll give this a try and report back.
Avatar of jer2eydevil88
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I guess Excel can't do this.