05-07-2019 04:31 PM
05-07-2019 04:31 PM
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.
05-07-2019 06:24 PMSolution
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)
- 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!
05-08-2019 10:49 AM
Thanks @Celia_Alves ! That got me going and I really appreciate the response and solution.
05-08-2019 11:07 AM
Really glad that I could help! Thank you for the feedback! :-)
09-30-2019 10:49 PM
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
09-30-2019 11:20 PM
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", "")
10-01-2019 06:24 PM
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.
10-01-2019 07:32 PM
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", "")
= 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.
10-02-2019 08:10 PM
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...
10-03-2019 01:36 AM
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?