Forum Discussion
Delete Rows Based On Cell Contents Matching Cell Contents in Other Worksheet
you can do the following:
- make sure your main list is an Excel Table
- add one column to your contact list
- in the columns added, write a formula like this:
= IF(OR(ISNA(MATCH(email cell, email column in the Spam sheet, 0),
ISNA(MATCH(email cell, email column in the Unsubscribed sheet, 0),
ISNA(MATCH(email cell, email column in the Undeliverablesheet, 0)
),"delete", "")
- you should get the word "delete" in all the rows that have an email that is included in at least one of the other 3 sheets.
- filter the table in the column by "delete" and deleted the rows filtered
Let me know if this works. Good luck!
I download lists like these from county websites so I can send mail to them as I'm a real estate investor. This one in particular has 1400+ entries, houses with code violations. Those 2 zip codes within the list are areas I do NOT want to waste marketing $ mailing to, because it's in an area I dont want to buy in, due to high crime rate, etc. So I need a way I can remove the rows with those zip codes, so I can then send the "scrubbed" list to my mail fulfillment company. Hopefully that wasn't overly detailed lol.
fuhrung, no overly detailed at all! It is important to understand the procedure. A small detail can make all the difference to the approach chosen to solve the problem.
Since you have Excel 2016, your data is not always structured the very same way, and for some sets of data, it seems this is a one-time task, I would suggest going with Power Query, which is a built-in tool in Excel newer versions. I would like to offer you 30 minutes of free training online to teach you some the basics of Power Query that you need to know to be able to perform this data cleansing operation. There is also the option of just filtering a table. Which is even simpler and still efficient. In exchange, I would ask you to post here the description of the process after you learn it. It seems that several people are looking for the answer to similar problems, and this would be the best way to help them too. What do you think?
Great! I will send you a private message to schedule.
I'm good with that.