I need to filter multiple email addresses on Excel. Our "email list" comprises of similar and unique domain names. The issue I am having is separating email addresses that have a magnitude of similar domain names in them. For example. I have 50 JP Morgan, 35 Citi Bank and 20 TD Bank Email Addresses under one column. My question is - how do I extract one of each (unique value) on a grander scale of 30,000 contacts.
-
Ouch... sounds like a job for some VBA to create an array of domain names (and another element to contain a "sample" email address) but your loop will need to check to see if the domain name portion of the email address being read is already in the array, if it is then move to the next else add it to the array with the associated person's email address... bearing in mind the script would not be efficient as it would need to scan 30,000 rows and you'll only always get the first occurrence of the domain name found...– KinnectusCommented May 31, 2017 at 12:35
-
4You might want to consider providing dummy data for your example. Exposing all of that personal data on a public site may well be breaking a few privacy laws ...– DavidPostill ♦Commented May 31, 2017 at 12:56
-
1Oh hey... free spam targets!– BurgiCommented May 31, 2017 at 12:58
-
Screen shot removed.– DavidPostill ♦Commented May 31, 2017 at 12:58
-
You want to break it into two halfs, get the half after the @ and filter on that. No, I don't know how to do this in excel :/– djsmiley2kStaysInsideCommented May 31, 2017 at 13:00
|
Show 3 more comments
1 Answer
This is what I've done for a similar task
- Create a new sheet with a "crosswalk table": in your case, domain name as the first column and company name as the second column.
- Then in the 30K contact table create a new company name column with a VLOOKUP formula that looks for the domain name in the crosswalk table to get the company name.
- Finally filter by the new company name column.
Let me know if you need more insight, or if I'm off target here.
-
Thank you, In fact, to be honest, I don't understand what you are saying can you please check this video I did, this is what I need unique values from company/domain, please check the video. Video - streamable.com/5z8d9 Commented Jun 1, 2017 at 6:37
-
video looks different from your ask, but seems the same as this: superuser.com/questions/1211913/how-to-sort-5-by-5-in-excel/…– gns100Commented Jun 1, 2017 at 19:19