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

NikToo

Board Regular
Joined
Sep 24, 2015
Messages
54
Office Version
  1. 365
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
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,699
Office Version
  1. 365
Platform
  1. Windows
Do you get an error such as #NAME? or something else?
 

NikToo

Board Regular
Joined
Sep 24, 2015
Messages
54
Office Version
  1. 365
It won't even let me press Enter. If if type =INDEX(FILTER there's no autocomplete option for it.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,699
Office Version
  1. 365
Platform
  1. Windows
In that case try
Excel Formula:
=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

Board Regular
Joined
Sep 24, 2015
Messages
54
Office Version
  1. 365

ADVERTISEMENT

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.

VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,699
Office Version
  1. 365
Platform
  1. Windows
Without knowing the layout of your data & where blanks may or may not be, it's very difficult to help.
 

NikToo

Board Regular
Joined
Sep 24, 2015
Messages
54
Office Version
  1. 365

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,699
Office Version
  1. 365
Platform
  1. Windows
Try
VBA Code:
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

Board Regular
Joined
Sep 24, 2015
Messages
54
Office Version
  1. 365
No, won't do it. I feel it should be something like this:

VBA Code:
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,127,944
Messages
5,627,754
Members
416,273
Latest member
lharnage7331

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top