Want to concatenate multiple row data in in cell along with its values if present. Emp Id should not come if no data available infromt of it

Question

I have data in below format

And Expecting output in below format

Answer 1

Using TEXTJOIN would be the easiest way, but if you wanted you can also use a VBA code.

Function Concat(rng As Range, Optional sep As String = " / ") As String
    Dim rngCell As Range
    Dim strResult As String
    For Each rngCell In rng
        If rngCell.Value <> "" Then
            strResult = strResult & sep & rngCell.Value
        End If
    Next rngCell
    If strResult <> "" Then
        strResult = Mid(strResult, Len(sep) + 1)
    End If
    Concat = strResult
End Function

In your case, you can use column F as a helper range and enter =Concat(A7:E7) in cell F2. This will generate the appropriate list back fitting your needs, minus the "-" between the EMP ID and the values. You can play around with the code to fix that if you desire, but this code will work for you.