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

by

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.

More Related Contents:

  1. Excel Number Separation [closed]
  2. Excel formula checklist in employees
  3. Sequence of numbers based on start and end value of 2 cells [closed]
  4. Error in finding last used cell in Excel with VBA
  5. Excel VBA – How to add dynamic array formula
  6. Filling any empty cells with the value above
  7. Find last used cell in Excel VBA
  8. Count unique values in Excel
  9. Concatenate column headers if value in rows below is non-blank
  10. How do I apply a SumIf Formula in a VBA Sub? Getting Error 1004
  11. Countif With Multiple OR Criteria
  12. How to extract text within a string of text
  13. How to roundup a number to the closest ten?
  14. Calculation is wrong, Rounding wrong
  15. Using a UDF in Excel to update the worksheet
  16. Excel’s fullname property with OneDrive
  17. How to insert an embedded picture?
  18. Obtain attribute names from XML using VBA
  19. Paste Excel range in Outlook
  20. Run-time error ‘1004’ : Method ‘Range’ of object’_Global’ failed
  21. “Can’t find Project or Library” for standard VBA functions
  22. How do I issue an HTTP GET from Excel VBA for Mac
  23. Copy sheet and get resulting sheet object?
  24. Export chart as image – with click of a button
  25. Error13 in Excel VBA in data file
  26. Excel VBA to match and line up rows
  27. Change font color for a part of text in cell
  28. Create a new object using the text name of the class
  29. Excel cell from which a Function is called [duplicate]
  30. GET pictures from a url and then rename the picture

Leave a Comment