Forum Discussion

TPCTech's avatar
TPCTech
Copper Contributor
May 07, 2019
Solved

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 ...
Formulas and Functions
  • Celia_Alves's avatar
    Celia_Alves
    May 07, 2019

    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!

     

RodwellSmith's avatar
RodwellSmith
Copper Contributor
Sep 30, 2019

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", "")

Celia_Alves's avatar
Celia_Alves
MVP
Oct 01, 2019

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.