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!
Sorry for the delay in responding.
There is no formula to delete a row. you can use formulas to point you the rows that have data that you want to delete, but a formula won't perform that action for you. For that, you would have to use a macro that can be written with VBA programming language.
Does it make sense?
I believe my question is related to this thread.
I couldn't figure out how to attach a file so I just took an example snip. I'm trying to scrub the list of certain zip codes. If the zip code in Sheet1 column I equals 68110 or 68111, I want to remove the row & transfer it to Sheet2. Is there a formula that can do that?
If this is possible, someone could then just delete Sheet2, thus solving the delete row.
Great! I will send you a private message to schedule.
I'm good with that.
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?
- Celia_Alves
And I have Excel 2007 & 2016 - Celia_Alves
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
There is no formula that makes data disappear from one place and reappear in another place. Only VBA could do that. But maybe we can think this problem on a different way. What is that you are trying to accomplish? Do you really need to delete those codes? It seems that what you need is a "clean" version of your data without the entries with those codes. Is this correct? If yes, then what we can do is to leave your data where it is and create a list, on a separate sheet with only the entries that you want to keep instead of the ones that you don't want. There are different ways to accomplish that.
Questions:
1 - how long is your data list?
2 - is this a one-time task or do you need to repeat it frequently with a new set of data?
3 - which version of Excel do you have?