Removing EXTRA dupes

**tommark** · 07-05-2013, 06:56 PM

I already use "data/remove duplicates", but have come up with a new dilemma. I'm using slightly outdated lists of email addresses to market for my clients, and constantly receive a list of "bounces" after I send out a number of emails. For example, I send out 1,000 emails, and 100 bounce. I now have two lists: the original 1,000 contacts, and the 100 bounces. Is it possible to remove the 100 bounces from the 1,000 list, leaving 900 valid email addresses without having to do it with Ctl-F, one at a time?

**TMS** · 07-05-2013, 07:04 PM

Add a helper column to the big list. Use COUNTIF to see if each one exists in the short list. 100 will have a value of 1, 900 will have a value of 0. Filter on the 1s and delete the visible rows. Then delete the helper column.

Regards, TMS

**FDibbins** · 07-05-2013, 07:04 PM

Hi and welcome to the forum

Without seeing your data, you may have to adjust ranges etc. Assuming list 1 (1000) is in A1 down, and list 2 is in C1 down, copy this down in B...

=vlookup(A1,$C$1:$C$100,1,0)

Then all the cells that have an error message in them, are the 1's you keep - if it gives an answer, that means it found a "bounce" and can be deleted

**BrownBoy** · 07-05-2013, 08:10 PM

Create a new column of data for column C. Give the cell C1 the column name "Match".

Assumptions:
1. Column A contains the list of 1000 email addresses.
2. Column B contains the list of 100 bounced email addresses.

Use the following formula:
=MATCH(B2,A2:A1001,0)
Copy the formula down the column.
You should only have 100 matches, the rest should be an #N/A error. Sort by column C and all your bounce addresses will be at the top of array. Delete out the values that had a match and then delete column C.

**TMS** · 07-05-2013, 10:49 PM

The match range would need to be absolute.

However, if you match column B (short list) against column A (long list) you will get 100 matches.

Regards TMS

**BrownBoy** · 07-06-2013, 01:58 PM

Thank you TMShucks. Forgot to make the range absolute.

**tommark** · 07-07-2013, 12:21 PM

Originally Posted by BrownBoy

Thank you TMShucks. Forgot to make the range absolute.

Thank you, but that doesn't seem to work. I created two columns in the big sheet: one for the countif function, the other with the list of EXTRA dupes that I want to delete from the "big list". The countif function works correctly, and tells me that there is 1 duplicate in the big list that matches each cell in the "to be deleted" list. So I end up with a column that has only "1"s in it. (I assume you mean "sort by" when you say "filter"). Sorting a column that consists of only the number 1 has no result.
Let me explain where I'm getting my lists. I emailed 2,000 addresses and got several hundred bounces. The "big list" is the list I emailed, and it includes not only email addresses, but mail address, phone numbers, names, etc.
The "to be deleted" list is all the bounced emails.
Using the "to be deleted" list, I want to quickly find all the matching files(rows) and delete them from the "big list", leaving me with a shorter list of non-bounced emails.
Can that even be done?
Again, thank you very much

Tom Markham

**newdoverman** · 07-07-2013, 12:55 PM

Just select all the data in the "big" list click on Sort and Filter, Custom Sort and sort on the column with the 1's. All the rows with 1 will be sorted together. Select those rows and delete.