+ Reply to Thread
Results 1 to 8 of 8

How to lookup values on a table and get column headers for filtered criteria?

  1. #1
    Registered User
    Join Date
    09-19-2013
    Location
    Kansas
    MS-Off Ver
    Excel 2007
    Posts
    4

    How to lookup values on a table and get column headers for filtered criteria?

    Hello,

    I am in need of the ability to lookup a part number on a table of parts and models. For certain models specified, I need to know if that model uses the part in question.
    The attached file is a sample of the dataset with more details.

    First time here. Looks like a great source for help.
    Attached Files Attached Files

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: How to lookup values on a table and get column headers for filtered criteria?

    I built a sort macro for the attached workbook that allows you to search all rows for matches, and then it automatically filters all nonmatching rows out.

    Parts List - Autosort Macro.xlsm

    Would something like this help you out?

  3. #3
    Registered User
    Join Date
    09-19-2013
    Location
    Kansas
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: How to lookup values on a table and get column headers for filtered criteria?

    I'm not sure how I would make that work for my application. We are generating order sheets for a specific set of models scheduled for production. The ability to display which model the part goes on, based on the filter, helps those doing the order to narrow down which parts need to be ordered or not. Thanks, though.

  4. #4
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: How to lookup values on a table and get column headers for filtered criteria?

    I did some toying with it and I think I can make it apply. It just needs 2 filters. One to filter the columns, and another to filter the rows.

    Then you will only see the relevant intersections.

    What does 2 represent on the matrix?

  5. #5
    Registered User
    Join Date
    09-19-2013
    Location
    Kansas
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: How to lookup values on a table and get column headers for filtered criteria?

    Quote Originally Posted by daffodil11 View Post
    I did some toying with it and I think I can make it apply. It just needs 2 filters. One to filter the columns, and another to filter the rows.

    Then you will only see the relevant intersections.

    What does 2 represent on the matrix?
    It's not a big deal. As long as the value is greater than zero, then the part could go on that particular installation. Thanks.

  6. #6
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: How to lookup values on a table and get column headers for filtered criteria?

    It's not particularly pretty, but you could concatenate INDEX/MATCHES.

    I'd recommend using TRIM on your headers, as they all have a number of spaces after each string.
    This will cause errors if you type model C232 and try to find a match against "C232 " which is what is in there now.

    In my attached version, I took the liberty of removing the trailing spaces.

    Here's the final formula I used:

    K20,K21,K22,K23,K24 are model numbers, and the value in K27 is the part.

    Please Login or Register  to view this content.
    partsmodel.png

    partsmodel - concatenation.xlsx
    Last edited by daffodil11; 09-19-2013 at 12:33 PM. Reason: Pictures are fun

  7. #7
    Registered User
    Join Date
    09-19-2013
    Location
    Kansas
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: How to lookup values on a table and get column headers for filtered criteria?

    That has got to be the longest formula I have ever seen. IT WORKS GREAT!!!!

    THANK YOU! Much respect!

  8. #8
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: How to lookup values on a table and get column headers for filtered criteria?

    No problem, glad I was able to help you out. I like pushing the boundaries of what I think I'm capable of doing.

+ 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: 0
    Last Post: 09-11-2013, 09:54 AM
  2. Macro to Change Column Headers Depending on What Column is being Filtered
    By Stormm in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-20-2013, 05:34 PM
  3. [SOLVED] How to select from a table of values based upon column and row headers
    By Peteryoull in forum Excel General
    Replies: 4
    Last Post: 07-30-2012, 05:58 AM
  4. Replies: 2
    Last Post: 05-09-2012, 01:30 PM
  5. [SOLVED] How do I use a lookup table to sum values for specific criteria?
    By NJLExcel1 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-08-2012, 02:57 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