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!
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.
This I must try. I am trying to delete the rows from one worksheet (MD and VA Contacts CSV) because the row contain any word or phrase from another list worksheet (Expired Contact Information List). Thank you Celia_Alves
Thanks Celia_Alves ! That got me going and I really appreciate the response and solution.
Really glad that I could help! Thank you for the feedback! :-)
- Thanks! That makes sense to me. I'll give it a shot.
