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!
What did I do incorrectly I receive the response too many Arguments? Celia_Alves
= IF(OR(ISNA(MATCH(A1..A56, 1059271022-domain-invalid, 0),),"delete", "")
hi,RodwellSmith
Please try the following:
= IF(ISNA(MATCH(cell with email address to search for on second sheet, column with emails on second sheet, 0)),"delete", "")
For example, if the first email address that you want to check is on cell A1 of sheet1 and the column with emails in sheet2 is column C, write this formula in the column of sheet 1 where you want to run the check:
= IF(ISNA(MATCH(A1, 'Sheet2!'C:C, 0)),"delete", "")
If it doesn't work, please feel free to send the file with some mock data and I'll see if I can help you.
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"
