+ Reply to Thread
Results 1 to 18 of 18

Find Column Number and then use Index Function

  1. #1
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Find Column Number and then use Index Function

    I have a database with over 100 products listed across the first row.
    Column a has a list of over 500 projects. Across each project various columns are marked with a number depending on how many of each products are being used on that project.
    For Example

    A B C D E etc.
    Products --> X Y Z AA
    Proj 1 2 3
    Proj 2 1 4 5
    Proj 3 2 4
    etc.

    I want to be able to create a report for any given product.
    The report could look like,
    Product Z
    Proj 1 3
    Proj 3 2

    So I need to lookup the product code across row 1 and determine the column number and then INDEX down that column and find all non blank cells and read the project names from column A.

    Thanks in advance for any help.
    I am familiar with formulas with INDEX and V/H LOOKUP functions. I am not very good with VBA codes.

    modytrane

  2. #2
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Find Column Number and then use Index Function

    Sorry about the formating of the example above.

    Here's a better example:

    Products ---->____X_____Y_____Z_____AA
    Proj 1____________2_____3_____0_____ 0
    Proj 2____________0_____1_____3_____1
    Proj 3____________4_____0_____2_____4

    Report:
    Product Z______Qty.
    Proj 2__________3
    Proj 3__________2

    Hope this makes it more clear.

    Thanks,
    modytrane

  3. #3
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Lookup Column Number and Index Down that Column

    Can someone please help me get started on this problem?
    I need a formula to search for a text string across a ROW and find the column number [or letter]. Then I need to look down that column and find any non blank cells [and its row number]. Then look across that row to pick out values from various columns across that row to generate a report.
    The example I gave in my previous message is a very basic. Once I get that figured out, I can write more complex ones with many more fields and search criteria.

    Thanks.
    mondytrane

  4. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    You need to attach an example workbook. I would suggest that you zip it first
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419
    Try this

    Data in A1:E4
    Reporting product in B9

    Output starts A11

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    See attached:

    Formulas in Sheet2:

    In A3:
    Please Login or Register  to view this content.
    in A4:
    Please Login or Register  to view this content.
    both formulas confirmed with CTRL+SHIFT+ENTER not just ENTER and then copied down.

    If you make changes reconfirm with CSE before copying down.
    Attached Files Attached Files
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  7. #7
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Find Column Number and Index that column

    Here's a workbook.
    Its a small portion of a larger workbook with example of a report on sheet2.
    Thanks for your help.
    modytrane
    Attached Files Attached Files

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    See attached:

    Your example with my formulas applied....
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Find Column and index down that column.

    Thanks a lot.
    It works just the way I wanted it.
    modytrane

  10. #10
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Find Row Number and Use Index across ROW

    I have another favor to ask.
    Your solution worked so well, that now we need to create another report.
    This time we would like to selelct a project and create a list of products and quantities used in the project.

    I am attaching a sample file.
    Sheet1 has your formulas from previous answer that worked so well.

    Sheet2 has the example of new report we would like.

    Thanks again,
    modytrane

  11. #11
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Quote Originally Posted by modytrane
    ...
    I am attaching a sample file.
    Sheet1 has your formulas from previous answer that worked so well.

    Sheet2 has the example of new report we would like.

    Thanks again,
    modytrane
    Please try attaching again....

  12. #12
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Find Row number and index across the ROW

    Sorry about that.
    Here's the attachment.
    Attached Files Attached Files

  13. #13
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    So what's supposed to happen in Sheet3?

  14. #14
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Find Row Number and then Index Across That ROW

    In Sheet3 user can select a project name from the drop down list in D3.
    Then the formula below that in D6 would find that project name in Sheet1 [Column C] and read across that ROW, find any cells that have a number in it, get product name from the top of that column and list that product in D6.
    Formula in E6 would list quantity for that product. Similar formulas would continue down D and E columns.

    In other words a report with products and quantities for the selected project.

    Thanks,
    modytrane

  15. #15
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    See attached.

    Formula in D6:

    Please Login or Register  to view this content.
    Formula in E6:

    Please Login or Register  to view this content.
    adjust ranges to suit and confirm with CTRL+SHIFT+ENTER and copy down.
    Attached Files Attached Files

  16. #16
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Find Row Number and Index Across The ROW

    Thanks a lot.
    It works great.
    I really appreciate quick response.
    modytrane

  17. #17
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    You are welcome! Thanks for the feedback!

  18. #18
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Find project in column and index across that row

    Thanks a lot.
    It works great.
    I appreciate rpommpt response.
    modytrane

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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