+ Reply to Thread
Results 1 to 7 of 7

Extract data based on multiple substring criteria

  1. #1
    Registered User
    Join Date
    10-08-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    4

    Extract data based on multiple substring criteria

    I need help on extracting all rows based on the criteria provided. I attached here the sample data(the original file has 40K rows with 15 columns))
    The sample file contains Data(raw files), criteria and output(expected output in getting all rows that meets the criteria)

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Extract data based on multiple substring criteria

    Sorry, misunderstood your requirements.

    Pete
    Last edited by Pete_UK; 10-09-2013 at 06:03 AM.

  3. #3
    Forum Contributor
    Join Date
    06-26-2013
    Location
    Makati
    MS-Off Ver
    Excel 2010
    Posts
    138

    Re: Extract data based on multiple substring criteria

    Hi jspharriola,

    The best way to do this is using excel advance filter, it might wont work with the code that has additional character in the front and end but it can be solve with adding Wildcard (*) in the front and end of the code criteria

  4. #4
    Registered User
    Join Date
    10-08-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Extract data based on multiple substring criteria

    Hi Pete,

    I need to extract all the rows that will meet my requirements. As you can see on my sample file I have a list of criteria which are not exactly the same with the code provided in data.

    Here is my current formula which i need to improve because i have to change every criteria after i got the first row.

    " =IFERROR(INDEX(A$3:A$13,AGGREGATE(15,6,(ROW($C$3:$C$13)-ROW($C$3)+1)/(ISNUMBER(SEARCH(IF($E$3<>"",$E$3,$F$3),IF($E$3<>"",$A$3:$A$13,$B$3:$B$13)))*ISNUMBER(SEARCH($G$3,$C$3:$C$13))),ROWS(I$3:I32))),"")

  5. #5
    Registered User
    Join Date
    10-08-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Extract data based on multiple substring criteria

    Hi Jul Stev,

    Thank you for your reply.

    How do i use wildcard searching in Advanced Filter.

  6. #6
    Registered User
    Join Date
    10-08-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Extract data based on multiple substring criteria

    Hi Guys,

    I finally solved my concern with Jul Stev solution.

    Thank you for the help.

  7. #7
    Forum Contributor
    Join Date
    06-26-2013
    Location
    Makati
    MS-Off Ver
    Excel 2010
    Posts
    138

    Re: Extract data based on multiple substring criteria

    Glad i can help, Thanks for the feedback

+ 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. extract rows from sheet based on multiple criteria
    By frederikk in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-05-2012, 04:12 AM
  2. [SOLVED] Extract Substring, then Ignore that Substring, while collecting data from Other substrings
    By Sameki121 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-17-2012, 05:21 PM
  3. Extract Data Based on Multiple Criteria
    By trevaaaaaaa in forum Excel General
    Replies: 2
    Last Post: 03-14-2012, 11:42 AM
  4. Replies: 2
    Last Post: 07-19-2011, 04:08 PM
  5. Replies: 2
    Last Post: 03-23-2011, 06:19 PM

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