INDEX MATCH to return column header based on empty value in row

[NikToo]

I feel I'm almost there with this but not quite getting it right. I have a list of product numbers, dates across the columns, and a number in the data. What I want to do is find the product number, find the first non-blank cell in the data and return the date in the column header. I've been staring at Index/Match and just going round in a circle now.

Basically it'll give me the date the product was first sold.

Thanks in advance

 

[NikToo]

What do you mean by "FILTER isn't a supported function"?

Excel doesn't recognise it.

 

[Fluff]

Do you get an error such as #NAME? or something else?

 

[NikToo]

It won't even let me press Enter. If if type =INDEX(FILTER there's no autocomplete option for it.

 

[Fluff]

In that case try

=INDEX($B$1:$E$1,MATCH(TRUE,(INDEX($B$2:$E$6,MATCH(H2,$A$2:$A$6,0),)<>""),0))

You may need to confirm it with Ctrl Shift Enter rather than just Enter.

 

[NikToo]

That works, but now the issue is trying to future proof it with some other stuff and not break stuff people have done. I'm wondering if this sort of macro would work, used it for something similar. Column AG on the Products By Qty Pivot (I didn't name them) has a 1 or 0 flag for a new product. This will find the row where the flag is 1 and write the value in column A (the -32 bit) into the NewProducts sheet.

What's baking my noodle is finding that row number, do an End(xlRight).Columns.Count, and then simply take that column number to get the date.

I've had a year off VBA which is why my brain is mush... I know what I want to do, but it's like trying to remember how to say something in French.

Sub WriteNewProductsInfo()
    Dim rng As Range
    Dim cel As Range
    Dim lastRow As Long
    Dim writeRow As Long
    Dim firstDate As Long
    
lastRow = Sheets("Products By Qty Pivot").Cells(Rows.Count, "AG").End(xlUp).Row
writeRow = Sheets("NewProducts").Cells(Rows.Count, "A").End(xlUp).Row + 1


'New Product Flag (1) in column AG

Set rng = Sheets("Products By Qty Pivot").Range("AG5:AG" & lastRow)
For Each cel In rng
    If cel.Value = 1 Then
        
        'Write New Product Code from Pivot to NewProducts tab
        Sheets("NewProducts").Range("A" & writeRow).Value = cel.Offset(0, -32).Value
        
        'Get column for first non-blank
        firstDate = something something cel offset something end(xlRight).columns.count
        
        'Write date based on column number
        Sheets("NewProducts").Range("B" & writeRow.Value = Sheets("Products By Qty Pivot").Range(4, firstDate).Value
        
        
        writeRow = writeRow + 1
    End If
Next cel
            
End Sub

 

[Fluff]

Without knowing the layout of your data & where blanks may or may not be, it's very difficult to help.

 

[NikToo]

It's the same layout as my table previously. So it would go down column AG, where it's a 1, write in the product code (like 222), then I want it to just count the columns from column A to the first non-blank, give me a number, then use that number in row 4 where the dates are (I didn't make the table...), and x number of columns along. Then the next. Maybe a For Next loop would work...

 

[Fluff]

Try

With Sheets("Products By Qty Pivot")
   Set rng = .Range("AG5:AG" & lastRow)
   For Each cel In rng
       If cel.Value = 1 Then
           
           'Write New Product Code from Pivot to NewProducts tab
           Sheets("NewProducts").Range("A" & writeRow).Value = cel.Offset(0, -32).Value
           
           'Get column for first non-blank
           If Range("B" & cel.Row) <> "" Then
               firstDate = .Range("B4").Value
           Else
               firstDate = .Cells(4, .Range("A", cel.Row).End(xlToRight).Column).Value
           End If
           'Write date based on column number
           Sheets("NewProducts").Range("B" & writeRow.Value) = firstDate
           
           
           writeRow = writeRow + 1
       End If
   Next cel
End With

 

[NikToo]

No, won't do it. I feel it should be something like this:

Sub WriteNewProductsInfo()
    Dim rng As Range
    Dim cel As Range
    Dim lastRow As Long
    Dim writeRow As Long
    Dim firstDateColumn As Range
    Dim firstDate As String
    
lastRow = Sheets("Products By Qty Pivot").Cells(Rows.Count, "AG").End(xlUp).Row
writeRow = Sheets("NewProducts").Cells(Rows.Count, "A").End(xlUp).Row + 1


With Sheets("Products By Qty Pivot")
   Set rng = .Range("AG5:AG" & lastRow)
   For Each cel In rng
       If cel.Value = 1 Then
           
           'Write New Product Code from Pivot to NewProducts tab
           Sheets("NewProducts").Range("A" & writeRow).Value = cel.Offset(0, -32).Value
           
              firstDateColumn = .Cells(cel.Row, 1).End(xlToRight).Column.Count
              
              firstDate = .Cells(4, firstDateColumn).Value

           'Write date based on column number
           Sheets("NewProducts").Range("B" & writeRow).Value = firstDate
           
           
           writeRow = writeRow + 1
       End If
   Next cel
End With
            
End Sub

With the line firstDateColumn = .Cells(cel.Row, 1).End(xlToRight).Column.Count kicking out a runtime error 424. Also tried Columns.Count, Column.Value, Columns.Value.

I'm spending more time on this than it would have taken to do it manually for the rest of the year...

 

[Fluff]

No, won't do it.

In what way?