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]

ticket number	SAM	JACK	PETER	DAVID	MARK	Who is pending
123456	12-10-2016	2-5-2014	pending	pending	3-5-2015
123457	pending	2-8-2015	2-9-2015	2-6-2017	5-9-2.014
123458	2-7-2011	pending	pending	pending	pending
123459	10-2-2015	4-9-2016	7-8-2014	7-5-2013	7-3-2017

<tbody>
</tbody>

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.