Returning header labels based on cell values

[sam aljanabi]

Hi ,
I need your help, i have a sheet that has several entries (columns and row, sample below) i would like to retrieve the header label based on a certain cell value (i.e. pending) and place the labels in the last cell (who is pending).
in other words, if there is a date in the cell then its ok otherwise raise a flag and point out that the ticket number for that individual is still pending and that ticket is not complete.

The output in the last cell should be as follows:
- First ticket [PETER, DAVID]
- Second ticket [SAM]
- Third ticket [ jack, peter, mark]
- Last ticket [ALL COMPLETE]


[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD]ticket number[/TD]
[TD]SAM[/TD]
[TD]JACK[/TD]
[TD]PETER[/TD]
[TD]DAVID[/TD]
[TD]MARK[/TD]
[TD]Who is pending[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]12-10-2016[/TD]
[TD]2-5-2014[/TD]
[TD]pending[/TD]
[TD]pending[/TD]
[TD]3-5-2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123457[/TD]
[TD]pending[/TD]
[TD]2-8-2015[/TD]
[TD]2-9-2015[/TD]
[TD]2-6-2017[/TD]
[TD]5-9-2.014[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123458[/TD]
[TD]2-7-2011[/TD]
[TD]pending[/TD]
[TD]pending[/TD]
[TD]pending[/TD]
[TD]pending[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123459[/TD]
[TD]10-2-2015[/TD]
[TD]4-9-2016[/TD]
[TD]7-8-2014[/TD]
[TD]7-5-2013[/TD]
[TD]7-3-2017[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks
Sam

 

[onlyadrafter]

Hello,

is this oK (in first who is pending cell)

=IF(B2="pending",B$1,"")&IF(C2="pending"," "&C$1,"")&IF(D2="pending"," "&D$1,"")&IF(E2="pending"," "&E$1,"")&IF(F2="pending"," "&F$1,"")

and copy down

 

[onlyadrafter]

Hello,

If your data has a lot of columns, which would make the IF formula length you could use VBA

Sub PENDING()
    For MY_ROWS = 2 To Range("A" & Rows.Count).End(xlUp).Row
        For MY_COLS = 2 To Cells(1, Columns.Count).End(xlToLeft).Column - 1
            If Cells(MY_ROWS, MY_COLS).Value = "pending" Then
                MY_COUNT = MY_COUNT + 1
                MY_NAMES = MY_NAMES & " " & Cells(1, MY_COLS).Value
            End If
        Next MY_COLS
        If MY_COUNT = 0 Then
            Cells(MY_ROWS, Columns.Count).End(xlToLeft).Offset(0, 1).Value = "ALL COMPLETE"
        Else
            Cells(MY_ROWS, Columns.Count).End(xlToLeft).Offset(0, 1).Value = MY_NAMES
        End If
        MY_NAMES = ""
        MY_COUNT = 0
    Next MY_ROWS
End Sub

Have assumed your data starts in Cell A1

 

[sam aljanabi]

Hello,
thanks for feedback, the first post worked fine, but i do have roughly 20 columns, and i am not that keen in VBA.