Removing entries in List B from List A

[shakazul]

Hey guys,

Sorry for posing this issue, I know it has been resolved multiple times on this forum, however I haven't been able to solve it with the solutions given.

I have an Excel file with a single workbook. In this workbook are two columns (read lists). List A: Total email addresses. List B: Faulty email addresses. I want to remove the addresses in List B from List A.

What would be the best way to do this? Manual is taking me forever.

Excel file in question is attached.

Thanks a bunch,

Shakazul.

[jeffreybrown]

In C2 copied down

=MATCH(B2,$A$2:$A$6647,0)

If B2 and down is found in column A then you will see a number, but if not, #N/A

[mike7952]

You can put this in cell C2 and copy down to the lastrow in column A. Filter the results in column C that equal 1 then delete the rows.

Please Login or Register  to view this content.

[mike7952]

I had my formula reversed try this

Please Login or Register  to view this content.

Thanks Jeff

[Fotis1991]

A simple way is to use this in C2 and copy down and then auto filter for non zeroes

=IF(COUNTIF(Criteria,A2)>0,"",A2)

[jake.masters]

Here's what I have done which works pretty well.

Take the invalid emails, copy them, and paste them ABOVE the regular email list in the same column. Highlight only that column and go to Remove Duplicates, and do not expand the selection. Since the faulty emails are on top, the first time excel goes through, it will recognize this one, then when it continues, it will remove the faulty email (the 2nd appearance) from the main list, which is below the others.

In the attachment, on the first sheet is how to set up the process, and on the 2nd sheet is the outcome.

As you can see, the invalid emails are still at the top, and there are the same number of them. Simply delete these from on top, and your list is complete. As you can see from the MATCH() function on the final sheet, none of the invalid emails occur in the mailing list.