want to remove gmail.com and other email domains from list of email address?

[tonywatsonhelp]

Hi everyone,
I have a workbook called "Email Macros1"
in Sheet1 column A I have a list of email domains I don't want to include in my email list.

basically, I have a list of companies with directors emails, but I'm not interested in the company if its not got its own email address as in tony@mybusiness.com, if it tony@gmail.com I'm not interested.

I've made a list of all the email domains I wish to exclude in Sheet1 Column A, these are the names only so no "@", just gmail.com etc.

and in Sheet 2 Column A I have a list of email Addresses. (there is some data in other rows like B C D as well,) these are full emails like Tony@gmail.com etc.

So I want to remove the rows from sheet 2 that contain email domains from sheet1

any idea how I could do this?

Thanks

Tony

 

[etaf]

you can probably get a better solution in VBA - but i dont know that so well

I would create a lookup and mark each entry and then filter

so something like
=IFERROR(INDEX(Sheet1!A:A, MATCH(MID(A1,FIND("@",A1,1)+1,30),Sheet1!A:A,0)),"YES")

and then filter on YES

 

[tonywatsonhelp]

Hi etaf,
thank you very much for your help, I put it into a macro that did all the filtering etc for me and it works great,

thanks for you help
Tony

 

[mole999]

how about recording a macro that finds the value @gmail.com and get to delete that one, then modify the code to replace for each of the extensions