Find Column Number and then use Index Function

**modytrane** · 05-28-2008, 11:40 AM

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

**modytrane** · 05-28-2008, 11:50 AM

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

**modytrane** · 05-29-2008, 11:35 AM

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

**royUK** · 05-29-2008, 11:45 AM

You need to attach an example workbook. I would suggest that you zip it first

**modytrane** · 05-29-2008, 12:03 PM

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

**NBVC** · 05-29-2008, 12:11 PM

See attached:

Your example with my formulas applied....

**Andy Pope** · 05-29-2008, 11:56 AM

Try this

Data in A1:E4
Reporting product in B9

Output starts A11

Sub x()

    Dim rngData As Range
    Dim rngProduct As Range
    Dim lngRow As Long
    Dim rngOutput As Range
    Dim lngCol As Long
    
    Set rngData = Range("B1").CurrentRegion
    Set rngProduct = Range("B9")
    
    lngCol = Application.WorksheetFunction.Match(rngProduct, rngData.Rows(1), 0)
    
    Set rngOutput = Range("A11")
    rngOutput.CurrentRegion.Clear
    
    For lngRow = rngData.Rows(1).Row + 1 To rngData.Rows(1).Row + rngData.Rows.Count - 1
        If rngData.Cells(lngRow, lngCol) > 0 Then
            rngOutput.Cells(1, 1) = rngData.Cells(lngRow, 1)
            rngOutput.Cells(1, 2) = rngData.Cells(lngRow, lngCol)
            Set rngOutput = rngOutput.Offset(1)
        End If
    Next
    
End Sub

**NBVC** · 05-29-2008, 11:58 AM

See attached:

Formulas in Sheet2:

In A3:

=IF(ROWS($A$1:$A1)>SUMPRODUCT((Sheet1!$B$1:$E$1=$B$1)*(Sheet1!$B$2:$E$4>0)),"",INDEX(Sheet1!$A$2:$E$4,SMALL(IF(Sheet1!$B$1:$E$1=$B$1,IF(Sheet1!$B$2:$E$4>0,ROW(Sheet1!$A$2:$A$4)-ROW(Sheet1!$A$2)+1)),ROWS($A$1:$A1)),1))

in A4:

=IF(ROWS($A$1:$A1)>SUMPRODUCT((Sheet1!$B$1:$E$1=$B$1)*(Sheet1!$B$2:$E$4>0)),"",INDEX(Sheet1!$A$2:$E$4,SMALL(IF(Sheet1!$B$1:$E$1=$B$1,IF(Sheet1!$B$2:$E$4>0,ROW(Sheet1!$A$2:$A$4)-ROW(Sheet1!$A$2)+1)),ROWS($A$1:$A1)),MATCH($B$1,Sheet1!$A$1:$E$1,0)))

both formulas confirmed with CTRL+SHIFT+ENTER not just ENTER and then copied down.

If you make changes reconfirm with CSE before copying down.