SOLVED

Delete Rows Based On Cell Contents Matching Cell Contents in Other Worksheet

Copper Contributor

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.

18 Replies
best response confirmed by TPCTech (Copper Contributor)
Solution

@TPCTech 

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!

 

Thanks! That makes sense to me. I'll give it a shot.

Thanks @Celia_Alves !  That got me going and I really appreciate the response and solution.

@TPCTech 

Really glad that I could help! Thank you for the feedback! :)

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 

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.

@RodwellSmith 

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...

 

 

 

 

@Celia_Alves 

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"

@RodwellSmith 

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?

 

@Celia_Alves 

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.

 

Excel snip.JPG

 

@fuhrung
There is no formula that makes data disappear from one place and reappear in another place. Only VBA could do that. But maybe we can think this problem on a different way. What is that you are trying to accomplish? Do you really need to delete those codes? It seems that what you need is a "clean" version of your data without the entries with those codes. Is this correct? If yes, then what we can do is to leave your data where it is and create a list, on a separate sheet with only the entries that you want to keep instead of the ones that you don't want. There are different ways to accomplish that.
Questions:
1 - how long is your data list?
2 - is this a one-time task or do you need to repeat it frequently with a new set of data?
3 - which version of Excel do you have?
@Celia_Alves
I download lists like these from county websites so I can send mail to them as I'm a real estate investor. This one in particular has 1400+ entries, houses with code violations. Those 2 zip codes within the list are areas I do NOT want to waste marketing $ mailing to, because it's in an area I dont want to buy in, due to high crime rate, etc. So I need a way I can remove the rows with those zip codes, so I can then send the "scrubbed" list to my mail fulfillment company. Hopefully that wasn't overly detailed lol.

@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?

@fuhrung 

Great! I will send you a private message to schedule.

1 best response

Accepted Solutions
best response confirmed by TPCTech (Copper Contributor)
Solution

@TPCTech 

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!

 

View solution in original post