We help IT Professionals succeed at work.

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

276 Views
Last Modified: 2014-02-23
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.
Comment
Watch Question

CERTIFIED EXPERT

Commented:
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.
CERTIFIED EXPERT

Author

Commented:
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.
CERTIFIED EXPERT

Commented:
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)
CERTIFIED EXPERT

Author

Commented:
There are a lot of these, how can I automate the removal?
CERTIFIED EXPERT

Commented:
(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.
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
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:

=COUNTIF($A$1:$A$10000,$A1)

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.

Thanks
Rob H
CERTIFIED EXPERT

Author

Commented:
In Workbook 1 (my master list) I have a header row that shows the following data:
Last Name      
First Name      
Company      
Office Phone      
Office Fax      
Mobile      
Email      
FWG      
IWG      
QWG      
PWG

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

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.
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
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?
CERTIFIED EXPERT

Author

Commented:
Yes and remove the lines from the first that have those emails.
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
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:

=MATCH(G2,Sheet2!$A:$A,0)

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:

=MATCH(A2,Sheet1!$G:$G,0)

Again will return row number or error.

Thanks
Rob H
CERTIFIED EXPERT

Author

Commented:
I'll give this a try and report back.
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Author

Commented:
I guess Excel can't do this.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.