Removal of records from excel

Question

I have two columns in Excel. In the first column I have a master list of email addresses. In the second column I have a list of email addresses that I wish to remove from the master column.

Please can someone help me so I can remove all the records from master column using the second column?

Answer 1

You could put all of them in one column and then sort. The ones that are duplicates can be removed.

Answer 2

Create a new column next to the column containing your master list.

Fill the top cell with the following:

=IF(ISNA(MATCH(A1,B$1:B$4,0)),"valid","invalid")

Then paint that formula over the entire column.

Where B$1:B$4 is your set of invalid email addresses, and A1 is a reference to the master list element on the same row as this cell.

For each row you will now have a "valid" or "invalid" list, and you can then use that to remove invalid e-mail addresses from the master column - try using autofilter.

Alternatively:

=IF(ISNA(MATCH(A1,B$1:B$4,0)),A1,"")

Will create a new copy of the master column with blanks where the invalid e-mail addresses were.