+ Reply to Thread
Results 1 to 10 of 10

Search a table with multiple search inputs and return all matches

  1. #1
    Registered User
    Join Date
    10-02-2014
    Location
    Sylmar, Ca
    MS-Off Ver
    2011
    Posts
    44

    Search a table with multiple search inputs and return all matches

    Hello All,
    I am looking to parse a table of information and based on a search that I enter via a message box, I want to return all relevant data in a message box or a new spreadsheet. I wrote examples in the sheet

    Thanks so much!

    Need_help.xlsx

  2. #2
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,099

    Re: Search a table with multiple search inputs and return all matches

    Hi, JDI,

    did you have a look at the Advanced Filter where the criteria range could hold the information that you want to filter the data (and not use an InputBox as a MsgBox is solely to display meassages and not to grab information)?

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  3. #3
    Registered User
    Join Date
    10-02-2014
    Location
    Sylmar, Ca
    MS-Off Ver
    2011
    Posts
    44

    Re: Search a table with multiple search inputs and return all matches

    Hi HaHoBe,
    I am familiar with this advanced filter but it doesn't work like I have in mind. I have a main page (its like a form) When I type for example part # 12C16 and Cap value 1200 I want it to search the records and find the matches. Basically a search function that can match multiple inputs. Got anything for that?

  4. #4
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,099

    Re: Search a table with multiple search inputs and return all matches

    Hi, JDI,

    but it doesn't work like I have in mind.
    Which is what I may doubt as I think the Advanced Filter would suit better than any way of trying to set up inputboxes wherethe information would be needed to be separated and brought in form in order to meet your cvriteria. You could consider to use an UserForm where you may put in the data in order to evaluate.

    Ciao,
    Holger

  5. #5
    Registered User
    Join Date
    10-02-2014
    Location
    Sylmar, Ca
    MS-Off Ver
    2011
    Posts
    44

    Re: Search a table with multiple search inputs and return all matches

    Hi HaHoBe,
    You are right in suggesting the advanced filter, I was wondering if that can be put into a macro were there is a search button, data would show up on a new sheet, the data to be parsed is hundreds of thousand of lines and columns. If I could do that I could make a new sheet where it pulls the data from the sorted data. I can show you my original if that would help, but I'll have to upload it somewhere else due to size.

  6. #6
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,639

    Re: Search a table with multiple search inputs and return all matches

    Something like this?
    Before clicking on the Green Button on the sheet, read the instructions in red that how to change the criteria and in which cells.
    Attached Files Attached Files
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  7. #7
    Registered User
    Join Date
    10-02-2014
    Location
    Sylmar, Ca
    MS-Off Ver
    2011
    Posts
    44

    Re: Search a table with multiple search inputs and return all matches

    Quote Originally Posted by sktneer View Post
    Something like this?
    Before clicking on the Green Button on the sheet, read the instructions in red that how to change the criteria and in which cells.
    Exactly,amazing, now how did you do this??? I don't see any code, I found somethings in the name manager, can you explain? I need to apply this concept to something much more complicated.
    Last edited by JDI; 10-06-2014 at 05:09 PM.

  8. #8
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,099

    Re: Search a table with multiple search inputs and return all matches

    Hi, JDI,

    just turn on the macro recorder when starting the Advanced Filter by hand will give you the basic sztructure of the code. Code is placed in Module1 in the VBE but should be slightly amended in order to make as few changes as possible for any adaptions:
    Please Login or Register  to view this content.
    Ciao,
    Holger

  9. #9
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,639

    Re: Search a table with multiple search inputs and return all matches

    Quote Originally Posted by JDI View Post
    Exactly,amazing, now how did you do this???
    I think Holger has explained it very well.

    Quote Originally Posted by JDI View Post
    I don't see any code, I found somethings in the name manager, can you explain? I need to apply this concept to something much more complicated.
    The code is on Module 1. The name manager contains the criteria and the tables defined in the advanced filter. You can apply this concept to your original workbook, once you know how does an advanced filter work and once you know that, record a macro while using the advanced filter and assign this macro to a shape or a command button (as per your choice) to re-use it in future without using advanced filter again and again while you change the criteria.

    Hope this helps.

  10. #10
    Registered User
    Join Date
    10-02-2014
    Location
    Sylmar, Ca
    MS-Off Ver
    2011
    Posts
    44

    Re: Search a table with multiple search inputs and return all matches

    Quote Originally Posted by sktneer View Post
    I think Holger has explained it very well.


    The code is on Module 1. The name manager contains the criteria and the tables defined in the advanced filter. You can apply this concept to your original workbook, once you know how does an advanced filter work and once you know that, record a macro while using the advanced filter and assign this macro to a shape or a command button (as per your choice) to re-use it in future without using advanced filter again and again while you change the criteria.

    Hope this helps.
    Hi sktneer, I ran into a problem with the advanced filter.
    I want to use it to return matches but if my data is like this

    H1 A
    H2
    H3 C
    H4 D

    and I search for

    H1
    H2
    H3 C
    H4 D

    It returns nothing. I want to find matches to the criteria i am searching for and the data has blank cells. I'm not searching for a blank cell I just search for H3 and H4, can you help?

    Thanks
    Last edited by JDI; 10-16-2014 at 12:26 PM.

+ 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. Replies: 6
    Last Post: 08-22-2014, 08:47 AM
  2. Replies: 0
    Last Post: 06-16-2013, 10:46 AM
  3. Search named range for multiple matches to critera and return date & values
    By a.mack123 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-18-2011, 10:07 AM
  4. Search table with multiple criteria and return row
    By grunwaldlove in forum Excel General
    Replies: 2
    Last Post: 11-11-2009, 11:18 AM
  5. Search using a value that can return multiple matches
    By fg4432 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-07-2007, 10:56 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