May 07 2019 04:31 PM
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.
May 07 2019 06:24 PM
Solutionyou 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!
May 08 2019 08:40 AM
May 08 2019 10:49 AM
Thanks @Celia_Alves ! That got me going and I really appreciate the response and solution.
May 08 2019 11:07 AM
Really glad that I could help! Thank you for the feedback! :)
Sep 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
Sep 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", "")
Oct 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.
Oct 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", "")
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.
Oct 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...
Oct 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"
Oct 11 2019 08:19 AM
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?
Dec 12 2019 10:19 AM
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.
Dec 13 2019 02:44 AM
Dec 13 2019 08:17 AM
Dec 13 2019 08:23 AM
Dec 16 2019 04:49 AM
@fuhrung, no overly detailed at all! It is important to understand the procedure. A small detail can make all the difference to the approach chosen to solve the problem.
Since you have Excel 2016, your data is not always structured the very same way, and for some sets of data, it seems this is a one-time task, I would suggest going with Power Query, which is a built-in tool in Excel newer versions. I would like to offer you 30 minutes of free training online to teach you some the basics of Power Query that you need to know to be able to perform this data cleansing operation. There is also the option of just filtering a table. Which is even simpler and still efficient. In exchange, I would ask you to post here the description of the process after you learn it. It seems that several people are looking for the answer to similar problems, and this would be the best way to help them too. What do you think?
Dec 18 2019 12:47 PM
I'm good with that.
Dec 23 2019 05:20 AM
Great! I will send you a private message to schedule.
May 07 2019 06:24 PM
Solutionyou 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!