Excel Lookup / Find

[ambrosez]

1. i have a list of email addresses in column A. about 3000
2. in column B I have a list of domains - about 400. A lot of email addresses in column A belong to these domains. But I am not able to find them through vlookup. The reason being that in column A an email address will be listed as [email protected]. But in column B (domain name column) it would be listed as abc.ca.

What i need to do is find all the records in column A that contain the domain names listed in Column B regardless of the extension .com or .ca and remove them.

right now i am doing them using custom auto filter and this is time consuming. please help!

[FDibbins]

Hi, welcome to the forum

1. If you just want to highlight them, you could use Conditional Formatting (This might not be what you need to use for 1000 records though)
Highlight the range, select Conditional Formatting from te Home Tab
select Format Only Cells that Contain/click the Format Only Cells With, and select Specific Text, enter abc, sele3ct a format color and "ok"

2. You could use a helper column and copy this down...
=IFERROR(SEARCH("abc",J3,1),"")
Then apply filters (home tab, at the end) and under the dropdown, uncheck Blanks
You will be left with a list that contains abc.

IOf you want, you can put the abc in its own cell and reference it, to make it more dynamic

[ambrosez]

thanks so much ford. i am sorry but I realize that i needed to be more specific.

1. i have a list of email addresses in column A. about 3000
2. in column B I have a list of domains - about 400. A lot of email addresses in column A belong to these domains. But I am not able to find them through vlookup. The reason being that in column A an email address will be listed as [email protected]. But in column B (domain name column) it would be listed as abc.ca.

What i need to do is find all the records in column A that contain the domain names listed in Column B regardless of the extension .com or .ca and remove them.

right now i am doing them using custom auto filter and this is time consuming. please help!

[FDibbins]

Can you upload a small (clean) sample of what you are working with, and what your expected outcome would look like.

[Speshul]

make a helper column C and use =replace(a1,"@abc.com",".ca") and vlookup against that perhaps

[Speshul]

Actually


=vlookup(LEFT(A1,FIND("@",A1)-1)&".ca",TABLE_ARRAY,COL_INDEX,FALSE)

[ambrosez]

excel problem.jpg

what i have to do is find all the website names in column C one by one to see if they exist in column a and remove them. so what i am doing is copying the domain name "wfmsinc" as listed in the column c of the image to filter it in column a to see if any email addresses against this domain exist or not and then deleting them. what im hoping is that i do this simultaneously for all the records in column c.

[ambrosez]

excel problem.jpg

what i have to do is find all the website names in column C one by one to see if they exist in column a and remove them. so what i am doing is copying the domain name "wfmsinc" as listed in the column c of the image to filter it in column a to see if any email addresses against this domain exist or not and then deleting them. what im hoping is that i do this simultaneously for all the records in column c.

[ambrosez]

please take a look at this image. im sure this will clearly explain what im doing manually and wish it could be done in one go. excel problem2.jpg

[ambrosez]

any luck with this?

[FDibbins]

Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff)

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.