+ Reply to Thread
Results 1 to 7 of 7

Search multiple columns for with multiple input for search and return all results

  1. #1
    Registered User
    Join Date
    08-12-2014
    Location
    CA
    MS-Off Ver
    2011
    Posts
    73

    Search multiple columns for with multiple input for search and return all results

    Hello,
    I am having a little trouble getting excel to give me back what I want. Based on inputs from H1 and I1 I want to seach columns A and B for matches and return them below H1. Ie, H1 is _1560 and I1 is 25-35. From this search columns A for 25-35, then column B for _1560 and return any number of results that match with columns C D E F included in the return. (If a match is found for A and B return ABCDEF Horizontal), i,e 25-35 _1560 is a match and columns A B C D E F show up under H1. Then after this I need to compare the value of column "C" against an input K1 +/- 15%, L1 and only display data that meets the criteria. I know this is very complicated.

    Thanks very much
    Samuel Lamphier
    Help2.xlsx

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    20,712

    Re: Search multiple columns for with multiple input for search and return all results

    A couple of items to mention:

    1. for the following code to work, you will need to change the value in L1 from 15 to .15
    2. I scoured your data and could not find the expected results that you show in H18 and accross
    3. While my code does exactly what you asked, it returns no results as I could not find any of the expected results in the highlighted section of your sheet.

    I have the data posted to Sheet1 in your workbook. You may wish to change the criteria in H1 and I1 and be assured you have expected results to match up.

    Please Login or Register  to view this content.

    How to install your new code
    1. Copy the Excel VBA code
    2. Select the workbook in which you want to store the Excel VBA code
    3. Press Alt+F11 to open the Visual Basic Editor
    4. Choose Insert > Module
    5. Edit > Paste the macro into the module that appeared
    6. Close the VBEditor
    7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

    To run the Excel VBA code:
    1. Press Alt-F8 to open the macro list
    2. Select a macro in the list
    3. Click the Run button

  3. #3
    Registered User
    Join Date
    08-12-2014
    Location
    CA
    MS-Off Ver
    2011
    Posts
    73

    Re: Search multiple columns for with multiple input for search and return all results

    Hi alansidman,
    Thank you for this, it works very well, I was wondering if this can be done non macro though, just using excel commands? I need this to run automatically when input come in from other sheet. This is part of a very complex spreadsheet. I was also wondering if I can change the tolerance to accept all values less than the "compare" value but only values some % greater than the "compare" value?
    Thank you very much for your help.

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    20,712

    Re: Search multiple columns for with multiple input for search and return all results

    I don't know how you would do it with formulas. Maybe someone else in the forum could offer up a solution. As to changing the tolerances, yes, but you would need to be very specific on how you wish that to happen.

  5. #5
    Registered User
    Join Date
    08-12-2014
    Location
    CA
    MS-Off Ver
    2011
    Posts
    73

    Re: Search multiple columns for with multiple input for search and return all results

    okay I'll ask around, I do have another question which you can probably answer since you are great with macros.
    I have written a macro:

    Please Login or Register  to view this content.
    When I manually change something in F77 to N77 it works great, however, all of these values are called from another sheet. When I change it on the other sheet, even though it shows up as changed... the macro does not run. How can I fix this?

    Thanks in advance!!!
    Last edited by alansidman; 08-22-2014 at 08:42 AM. Reason: added code tags

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    20,712

    Re: Search multiple columns for with multiple input for search and return all results

    Code Tags Added
    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



    (Because you are new to the forum, I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    20,712

    Re: Search multiple columns for with multiple input for search and return all results

    I believe that worksheet change events do not fire if there is a recalculation, the change is the result of a formula update, etc. It requires an actual input to the target cells.

+ 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. Search a cell for multiple words and return multiple results in one cell
    By samanthat86 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-23-2013, 12:01 PM
  2. Search Workbook for cells that meet multiple criteria and return results from rows
    By CFritz7 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-27-2013, 04:41 PM
  3. [SOLVED] Search multiple columns and return heading
    By Catsup in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-07-2012, 11:41 PM
  4. Search multiple sheets and return list of multiple results
    By Abuck in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 03-07-2012, 11:59 AM
  5. User Form to Search multiple Criteria to return multiple Results
    By Calieth in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-16-2011, 11:57 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