Concatenate column headers if value in rows below is non-blank

by

Use¹ the following as an array formula.

=TEXTJOIN("-->", TRUE, IF(LEN(C3:I3), C$2:I$2, ""))

enter image description here

Pre-Excel 2016 versions

While you could just string together a series of IF statements, a cleaner alternate might be to write a user defined function (aka UDF).

In a standard VBA module code sheet:

Function udf_Stitch_Together(r As Range, _
                             h As Range, _
                             Optional d As String = "-->", _
                             Optional blnks As Boolean = False) As String
    Dim s As String, c As Long
    For c = 1 To r.Cells.Count
        If CBool(Len(r.Cells(c).Text)) Then _
            s = s & IIf(Len(s), d, vbNullString) & h.Cells(c).Text
    Next c
    udf_Stitch_Together = s
End Function

enter image description here

¹ The TEXTJOIN was introduced with Excel 2016 in the following versions:Excel for Android phones, Excel Mobile, Excel 2016 with Office 365, Excel 2016 for Mac, Excel Online, Excel for iPad, Excel for iPhone and Excel for Android tablet.

More Related Contents:

  1. 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
  2. Excel Number Separation [closed]
  3. Calculation is wrong, Rounding wrong
  4. Excel formula checklist in employees
  5. Sequence of numbers based on start and end value of 2 cells [closed]
  6. Error in finding last used cell in Excel with VBA
  7. Excel VBA – How to add dynamic array formula
  8. TEXTJOIN for xl2010/xl2013 with criteria
  9. Vlookup using 2 columns to reference another
  10. Filling any empty cells with the value above
  11. Find last used cell in Excel VBA
  12. Count Unique values with a condition
  13. Count unique values in Excel
  14. How can I run VBA code each time a cell gets its value changed by a formula?
  15. Fill formula down till last row in column
  16. Optimizing Excel formulas – SUMPRODUCT vs SUMIFS/COUNTIFS
  17. How do I apply a SumIf Formula in a VBA Sub? Getting Error 1004
  18. Countif With Multiple OR Criteria
  19. How to extract text within a string of text
  20. How to roundup a number to the closest ten?
  21. we want to search for an empty column and add items to that column using a vba form
  22. How to set recurring schedule for xlsm file using Windows Task Scheduler
  23. Iterating through populated rows
  24. Using VBA to detect which decimal sign the computer is using
  25. How do I click a link on a web page using Excel VBA?
  26. Ping IP Address with VBA code and return results in Excel
  27. Multiply Entire Range By Value?
  28. VBA Excel – IFERROR & VLOOKUP error
  29. Finding a workbook in one of multiple Excel instances
  30. excel vba how to copy the value of multiple non-contiguous ranges into an array

Leave a Comment