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

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 = "https://stackoverflow.com/") 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.

Browse More Popular Posts

Leave a Comment