+ Reply to Thread
Results 1 to 10 of 10

Search Keywords and Delete Every Line Except Keywords

  1. #1
    Registered User
    Join Date
    08-05-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    22

    Question Search Keywords and Delete Every Line Except Keywords

    Hello Guys,

    I have an excel file which consist of two sheets.

    Sheet1 is a data file and Sheet2 on Column A (From A1 to A100) contains specific keywords

    What I need is a macro to lookup all keywords from sheet2 on column A and search for those keywords on Sheet1, column B, C, and D, and delete every row except rows with Keywords.

    Thank you

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Search Keywords and Delete Every Line Except Keywords

    Hi,

    You may not need a macro, although of course you could encapsulate the procedure below in a macro. I'm reading this as you are wanting to delete rows from the Data sheet which don't have one of the keywords in columns b,c or d somewhere in the list of keywords in sheet2.

    Create a helper column, say E on the data sheet with the following formula in E1
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Copy E1 down the whole of your data
    AutoFilter column E for the value 'delete'
    Select all the filtered rows and delete them
    Remove the Autofilter
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    08-05-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    22

    Re: Search Keywords and Delete Every Line Except Keywords

    I get #N/A as the results. I also want this to be an automatic process. Appreciate any help!

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Search Keywords and Delete Every Line Except Keywords

    The #N/A suggests that you are trying to match the B,C & D values on a blank column A on Sheet2 - assuming you've not changed the formula

    If you want it automated it will need a macro. I suggest you upload the workbook so that we can advise further.

  5. #5
    Registered User
    Join Date
    08-05-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    22

    Re: Search Keywords and Delete Every Line Except Keywords

    I created a simple example File to show the similar pattern of what I need.

    Appreciate your help
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Search Keywords and Delete Every Line Except Keywords

    Try this:
    Please Login or Register  to view this content.

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Search Keywords and Delete Every Line Except Keywords

    AdvancedFilter.
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    08-05-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    22

    Re: Search Keywords and Delete Every Line Except Keywords

    Quote Originally Posted by Phuocam View Post
    Try this:
    Please Login or Register  to view this content.
    Works just fine. Thank you so much!

  9. #9
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Search Keywords and Delete Every Line Except Keywords

    Hi,

    Another option with the formula below in helper column E copied to E2:E10. (And if necessary have the macro add this formula for you)
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Run the following macro

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    08-05-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    22

    Re: Search Keywords and Delete Every Line Except Keywords

    Quote Originally Posted by Richard Buttrey View Post
    Hi,

    Another option with the formula below in helper column E copied to E2:E10. (And if necessary have the macro add this formula for you)
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Run the following macro

    Please Login or Register  to view this content.
    Works As Well. Thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] search by DOUBLE keywords
    By makinmomb in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-09-2015, 01:32 PM
  2. [SOLVED] VBA looking for multiple keywords and return concatenated keywords, that were found
    By Kpacu007 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-10-2014, 06:03 AM
  3. [SOLVED] Search for keywords and copy rows containing keywords to new sheet
    By lenorsk in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-15-2013, 06:54 AM
  4. [SOLVED] HDI - Return multiple keywords in a text column based on a set of available keywords
    By zneiley in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-25-2013, 01:32 PM
  5. To search Keywords
    By narendrabr in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-16-2013, 07:03 AM
  6. [SOLVED] Search By Keywords
    By Auni in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-19-2013, 07:00 AM
  7. Keyword search, several keywords
    By Doman in forum Excel General
    Replies: 1
    Last Post: 07-24-2006, 06:05 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1