Duplicates (2 files with different layout)

Hello,

I'm facing a problem and have no idea how to solve this.

Here is my problem:

I have 2 excel files. 1 has 10 columns and about 20.000 rows. Each row is a customer entry and each column specifies data about the customer (column 1 first name, column 2 last name, column 3 email, column 4 city and so on). The 2nd excel file only contains 1 column with 4000 email addresses in it. These are email addresses of people who opted out of our newsletter and other marketing channels.

Now I want to have excel delete all the rows of the first file that correspond to the email addresses of the 2nd file. Obviously the email addresses in both files are identical, though the 2nd one contains fewer entries and only those people we don't want to continue sending emails to.

Could some excel master step out and show me the light? I would truly appreciate this as I've spent a bunch of time and nerves already googling around but the advice given usually only applies if the data of both excel files is the same.

Thank you very much,
John M.

Copy the 4000 rows from the second file into the first file and set it up to align on the same column as the e-mail address

Next Conditional Formatting >> Highlight Cell Rules >> Duplicate Values...this will result in all duplicates color coded

Next set up the filter to filter by color

Delete all the rows in one go

Thanks jubiesxl and alansidman, for the quick replies!

I tried your suggestion, jubiesxl, and it worked the way I wanted to. I just did a slight change because once I tried to "filter by color", excel for some reason would just color the whole email column in red. Instead, I selected "sort by color" and that did the trick.

Thank you very much again Problem solved!

Glad it worked for you john.miller...

I was thinking of all the combination (formulas, functions, codes etc), but then took a step back and asked myself wwjd...intuition got me going the way of the above posted solution