Nov 30 2018 06:30 AM
Hi All,
Thanks in advance for helping me out. I have a situation where I have a column of domains which maybe around 500 & I have another column which has around 58k email addresses. Now, what the intention is that, I have to find out that among 58k email addresses how many are matching the 500 domains and list them. Below is what I already tried:
Advanced filter, V lookup & H lookup, but I am not able to get what I am looking for, so kindly help me as I am not that really good in Excel.
I have also attached an example's screenshot, so that I can explain myself better.
Thanks & regards,
PoorMens_Bravo
Nov 30 2018 07:19 AM - edited Nov 30 2018 07:21 AM
Hi,
I would suggest this formula:
=SUMPRODUCT(--(ISNUMBER(SEARCH(C2,$A$2:$A$14))))
Regards
Dec 10 2018 05:09 AM
HI Amairah,
Thanks for the reply, what I am looking for is the list where email address matches the domains and those to be filtered out and not the count of how many are actually existing.
Dec 10 2018 08:19 AM
Hi,
You can use the Advanced Filter as below screenshot:
Please note that the header of the List Range and the Criteria Range should be the same.
To learn more about the Advanced Filter, please see my reply on this similar question.
As the screenshot above, you need to surround each domain name with an asterisk character.
But since you have almost 500 names, I think you want a formula to automate this task.
So, I suggest using this formula:
=REPLACE(REPLACE(A1,1,0,"*"),LEN(A1)+2,0,"*")
Hope that helps
Dec 10 2018 09:48 AM
I thought the column" Custom" could have been what you wanted