Scrubbing Mass Email List

powellku

New Member
Joined
Aug 13, 2020
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
Hi Excel Gurus,

I've been working on this problem for days and tried numerous different formula to no avail, any help is appreciated!

Problem: I have a list of 250,000 emails that needs to have all emails associated with lawyers removed (ex. info@lawofficexyz.com).

Attempted Solutions: In sheet 1, I've split all emails into their root (ex. "info") and their domain (ex."lawofficexyz") to create two additional separate columns. In sheet 2, I have a list of 2250 words that are associated with lawyers emails ranging from strings (ex. "law" which should catch "lawyer") as well as specific domains and specific emails that I need to get rid of. I've attempted to use Vlookup, Match, Index, Countif, Search, IF formulas in different combinations. Typically, the object of the function is in sheet 1 and then the array of the function is in sheet 2.

Persistent Issue:
1) Too broad, when using an approximate match or partial string, my formulas identify every email rendering the formula useless. 2) Too narrow, when using exact match my formulas miss a lot of lawyer's emails which is missing the objective.

I can't seem to crack this one. Thank you again for you help! :)
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Have you tried using wildcards with your lookups? It might just be the same as an approximate match but I haven't seen your data so I thought I'd throw it out there.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top