+ Reply to Thread
Results 1 to 7 of 7

Delete rows from Sheet1 with same email field in Sheet2

  1. 09-13-2022, 02:15 PM #1
    kjxavier
    kjxavier is offline
    Registered User
    Join Date
    07-20-2011
    Location
    Kerala, India
    MS-Off Ver
    Excel 2013
    Posts
    93

    Thumbs up Delete rows from Sheet1 with same email field in Sheet2

    Hi! I use Windows7 with MS Office Professional Plus 2010

    I have attached a sample Excel file with
    Sheet1: Containing data from a lead collections software
    Sheet2 Column A: some common email domains

    Sheet1: Column B - contains Email ID's of the leads collected

    My requirement is to remove all the common email domain( listed in Sheet2 Column A) ROWS from Sheet1
    The results can either be edited on the same Sheet1 or on a Fresh Sheet.

    The result sheet should not contain any rows with "common email domains"
    As in Sheet3: (which I have done manually using filters)

    Thank you
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 09-13-2022, 02:52 PM #2
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Delete rows from Sheet1 with same email field in Sheet2

    I changed the email extension to .com


    =IFERROR(IF(MATCH(RIGHT(B2,LEN(B2)-SEARCH("@",B2)+1),Sheet2!$A$1:$A$4,0)>0,"delete"),"")


    See the attached file.
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.
    Reply With QuoteRegister To Reply
  3. 09-14-2022, 01:44 AM #3
    kjxavier
    kjxavier is offline
    Registered User
    Join Date
    07-20-2011
    Location
    Kerala, India
    MS-Off Ver
    Excel 2013
    Posts
    93

    Re: Delete rows from Sheet1 with same email field in Sheet2

    This does not fulfill the requirement.
    And the search should be for the domain not extension (eg: @gmail, @hotmail, @aol, etc)
    Reply With QuoteRegister To Reply
  4. 09-14-2022, 02:08 AM #4
    shukla.ankur281190
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Delete rows from Sheet1 with same email field in Sheet2

    Try

    =IF(SUM(--(ISNUMBER(SEARCH(Sheet2!$A$1:$A$4,Sheet1!B2))))>0,"DELETE","")
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Reply With QuoteRegister To Reply
  5. 09-14-2022, 06:42 AM #5
    kjxavier
    kjxavier is offline
    Registered User
    Join Date
    07-20-2011
    Location
    Kerala, India
    MS-Off Ver
    Excel 2013
    Posts
    93

    Re: Delete rows from Sheet1 with same email field in Sheet2

    Nothing happened!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 09-14-2022, 06:47 AM #6
    shukla.ankur281190
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Delete rows from Sheet1 with same email field in Sheet2

    The formula is an array formula must confirm with control+****+enter as you MS office version showing 2013.

    or try

    =IF(SUMPRODUCT(--(ISNUMBER(SEARCH(Sheet2!$A$1:$A$4,Sheet1!B2))))>0,"DELETE","")
    Reply With QuoteRegister To Reply
  7. 09-15-2022, 01:26 PM #7
    kjxavier
    kjxavier is offline
    Registered User
    Join Date
    07-20-2011
    Location
    Kerala, India
    MS-Off Ver
    Excel 2013
    Posts
    93

    Re: Delete rows from Sheet1 with same email field in Sheet2

    Thanks a lot. It works perfect
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Mcro to Delete Rows on Sheet1 where Ref No on Sheet2 is the same
    By Howardc1001 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-17-2020, 02:37 AM
  2. extractin data from sheet1 into sheet2+adding&deleting rows in sheet1
    By xxl2953 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-18-2016, 05:05 AM
  3. Move rows from sheet1 to sheet2 based on a field value
    By ivanka2000 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 02-19-2016, 08:15 AM
  4. Delete rows based on matching dates in Sheet1 Column A with date in Sheet2 Cell K1
    By robertaw in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-24-2015, 02:30 PM
  5. Need a macro to delete data on sheet2, copy data from sheet1 to sheet2
    By noorie007 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-03-2014, 02:32 PM
  6. match data of sheet1 and Sheet2 ColumnA, if matched copy columnB of sheet1 to sheet2
    By annielle_09 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-25-2013, 02:58 PM
  7. Delete from Sheet1 the rows that are also on Sheet2
    By email4kh in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-28-2010, 10:48 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1