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!
Thank you very much for replying,Celia_Alves
I have rearrange and table the format to have both sheets in the same workbook. "Sheet 1" is the cell with the subject "invalid domain" to search for then delete row from registered contacts or "Sheet 2". (Pardon me, I renamed the sheets to keep my focus) Here is the new formulary accordingly
= IF(ISNA(MATCH(A1, Sheet 2!'E:E, 0)),"delete", "")
I think we are onto something now! However, so I am not mistaken; which sheet am I making the column that will contain the word "delete" I tried both sheets and
= IF(ISNA(MATCH(A1, 'Sheet2!'E:E, 0)),"delete", "")
then
= IF(ISNA(MATCH('Sheet 1!'A1, 'Sheet2!'E:E, 0)),"delete", "")
I think I may have missed something in placing the formula. Please find attached two snippets with Excel's responses.
AgainThank you for helping me.
You can put the formula in either sheet, depending on what you need. It seems that you still need to do other things, first. You mentioned domains and registered contacts. MATCH will look for the same string in the column that you indicate. For example, in your first formula, if you have an email address in cell A1, the formula will look for that same string of email address in column E of sheet2. If that email address does not exist in column E, then the formula return an error (N/A)
In that case, the formula ISNA returns value True.
Finally, the formula IF will return "delete."
This would mean that you are looking to delete the email address in cell A1. Of course, the formula does not delete the email address from Sheet2, only makes note of it. 🙂
Maybe it is not quite this you are looking for...
Thank you for making more sense of this than I could. the email addresses I am trying to delete from the registration data are from expired domains. I have been experimenting with how to delete them from Sheet-2 which has the table of registrants. Sheet-1 has the list of emails from inactive domains. I have versed the formula so I can make sense and focus on the objective. Sheet-1 will change but I wish to maintain Sheet2 less the rows deleted by the objective formula:
How could I write this formula in Excel?
"If contents of a cell on Sheet-1 (say A1) match any content of cells in column E on Sheet-2 (say E2..E15000) delete the entire row on Sheet-2"
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.