Forum Discussion
Delete Rows Based On Cell Contents Matching Cell Contents in Other Worksheet
Hello All,
This is my first visit and my first post in this group. I haven't been able to find the answer to an issue I have so here I am. I have worked with Excel and formulas for many years but I am stumped in figuring this one out. Here is the scenario:
I have a contact list in one worksheet containing names, company and email address. I have other worksheets which contain email addresses of contacts who have marked my emails as Spam, Unsubscribed or are undeliverable.
My challenge is to delete the rows in the main contact list that contain the email addresses that are found in the other worksheets. I'm dealing with thousands of contacts and the list updates will be ongoing so I really need to automate this.
Thanks for any help you can provide.
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!
18 Replies
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.
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! :-)
