Returning header labels based on cell values

sam aljanabi

New Member
Joined
Jul 24, 2017
Messages
2
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 numberSAMJACKPETERDAVIDMARKWho is pending
12345612-10-20162-5-2014pendingpending3-5-2015
123457pending2-8-20152-9-20152-6-20175-9-2.014
1234582-7-2011pendingpendingpendingpending
12345910-2-20154-9-20167-8-20147-5-20137-3-2017

<tbody>
</tbody>


Thanks
Sam
 

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,615
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

Well-known Member
Joined
Aug 19, 2003
Messages
5,615
Hello,

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

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

New Member
Joined
Jul 24, 2017
Messages
2
Hello,
thanks for feedback, the first post worked fine, but i do have roughly 20 columns, and i am not that keen in VBA.
 

Forum statistics

Threads
1,081,726
Messages
5,360,906
Members
400,602
Latest member
newaqua

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top