Search Sheet for Text in range, delete rows not containing the text

  • Hi,
    I currently have workbook that has an UserInput sheet where a user will enter order numbers in cells A2:A33 that they are interested in.
    They then can use a macro to import data from a different database. This data that has been imported has all the orders for the last 6 months. How can I use the range on the sheet "UserInput" to search for those orders on the new "output-2" sheet and delete all the other orders?
    Should I create an array for the range A2:A33? Then how do I keep only those orders we are interested in?


    Thanks in advance for your help,


    Anne

  • Re: Search Sheet for Text in range, delete rows not containing the text


    Hi Anne,


    Why don't you attach your workbook ...in order to get a precise answer ... (much better than general comments on the feasibility...)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: Search Sheet for Text in range, delete rows not containing the text


    I've attached an example.
    The first tab has a list of order numbers that are the orders of interest.
    The second tab is all the imported data, the first column (A) has the order numbers for the last six months. I'm trying to build a macro that deletes all orders that are not listed in the first tab (range A2:A33).


    Thanks for your help

  • Re: Search Sheet for Text in range, delete rows not containing the text


    Hi again ...


    Thanks for the file ...:wink:


    You can test following macro :


    HTH

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: Search Sheet for Text in range, delete rows not containing the text


    Thanks!
    I have a second question.
    In one column of cells that each have a string of text example:
    XXX123456, pur4598-QC, XXX123455 pur939-www, XXX123444
    I would like to find all the numbers that start with XXX and the 6 digits after them and add them to cells to the right there could be one number or up to 4 per cell.
    See attached file.

  • Re: Search Sheet for Text in range, delete rows not containing the text


    Glad the initial problem is solved ...


    Regarding the second question, you are probably dealing with Column D ... but there is no indication of the result you are expecting ...


    Could you clarify ...???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: Search Sheet for Text in range, delete rows not containing the text


    No prob.
    if the text line is "XXX123456, pur4598-QC, XXX123455 pur939-www, XXX123444"
    The next cells should have "XXX123456", "XXX123455" and "XXX123444" respectively.
    See new attached file

  • Re: Search Sheet for Text in range, delete rows not containing the text


    OK ...


    You could test in cell E2 ... the following formula :


    =SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(D2,"pur4598-QC",""),"pur939-www","")),", ,",",")


    The formula extracts the requested fields ... but does not distribute them in the following columns ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: Search Sheet for Text in range, delete rows not containing the text


    In order to make that work I would have to know what text to exclude in each line. Since the database requires a user to update this field when submitting an order we have no control over how much "extra" information is inbetween the ID numbers that we want (in this example the "xxx123456" is the ID number type of interest) and how many ID numbers they will enter per order. Each ID number we are interested in all start with the same three letters "XXX" and then followed by 6 digits.
    I thought I could Parse the information by searching for the 6 digits after the text "XXX" and add XXX + "the 6 digits found" to the adjacent cell and looping that 4 times per cell to find all the unique ID numbers entered.
    Is this possible?

  • Re: Search Sheet for Text in range, delete rows not containing the text


    Attached is an example of the Substitute() function applied to all records ...


    In columns F to I, the respective fields are extracted ...


    HTH

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: Search Sheet for Text in range, delete rows not containing the text


    Here's another...


    * Put all your values in said range into a 1 Dimensional array (strings).
    * Autofilter using xlFilterValues (7) as the Operator.
    * Copy the result to another sheet..


    Note: If you wanted to just delete the rows that were 'invisible' after autofilter... you could loop through the range and build a new range (of hidden rows) using Union Method and then delete at the end..


    Code
    1. Private Sub CommandButton1_Click()
    2. Dim x
    3. x = Split(Join(Application.Transpose([A2:A31]), vbCrLf), vbCrLf)
    4. With Sheets("output-2").[A1].CurrentRegion
    5. .AutoFilter 1, x, 7
    6. .SpecialCells(12).Copy Sheets("Sheet1").[A1]
    7. .AutoFilter
    8. End With
    9. Sheets("Sheet1").Columns.AutoFit
    10. End Sub
  • Re: Search Sheet for Text in range, delete rows not containing the text


    I see what you did there and that would work if all the cells had the same information that I could substitute out. In this example assume that "pur4598-QC" and "pur939-www" can be any different string of text for each line. If I try to substitue the cell information I don't want for "," and apply it in the real data set (can not post here due to private information) I would have to update the cell information every time I run the macro since each order is unique.
    What I really want to do is Search for the ID by looking for the first three letters in a string and capture the next six characters, then paste that search 3 letter string and the 6 captured digits in another cell.


    sample text:
    "Blahblahblah XXX111111 yahddahahhadda XXX222222 andfinally-someother stuff XXX333333"
    where all I want to know is
    XXX111111, XXX222222 and XXX333333

  • Re: Search Sheet for Text in range, delete rows not containing the text


    1st problem


    2nd problem

  • Re: Search Sheet for Text in range, delete rows not containing the text


    Thank you. That worked perfect.
    Do you mind breaking down the second part a bit and explaining what it's doing?

  • Re: Search Sheet for Text in range, delete rows not containing the text


    Google "Regular Expression" "VBA" to get the idea.


    Just give you a brief explanation
    One of its ability is to pick matched string within a string by its pattern.
    it matches to "XXX" and numbers "\d+"
    \d is called Meta character that holds special meaning. number in this case, equivalent to [0-9]

  • Re: Search Sheet for Text in range, delete rows not containing the text


    @ jindon


    Thanks a lot ... both for the regexp solution you coded ... and for the explanation about the pattern for Regular Expression :smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)