Excel: Join with Headers

Sometimes you get a spreadsheet with lots of columns, with each column a comment from one person. Wouldn't it be nice to combine these columns into a single column?

If you just concatenate the comments, you end up losing the author of the comment. 

Using O365 Array formulas

Office 365 now has array formulas. Array formulas apply a single formular to each corresponding cell. Therefore we can now use a single formula:

=TEXTJOIN(                         // Join all the rows using...

    CHAR(10),                          // separator: carriage return

    TRUE,                              // ignore blanks: TRUE

    IF(

        B5:C5 = "",                    // If the cell is blank then

        "",                               // return a blank

        B$2:C$2&": "&B5:C5                // otherwise add the corresponding header

    )

)

Using Helper Columns

The easiest way is to add some helper columns:

Using VBA formulas

Or you could use the below VBA formula.

Public Function joinwithheaders(headers As Range, text As Range, separator As String) As String

    joinwithheaders = ""

    n = 0

    For Each head In headers

        n = n + 1

        If text.Item(n).Value <> "" Then   ' Skip blanks

            joinwithheaders = joinwithheaders & headers.Item(n)

            joinwithheaders = joinwithheaders & ": "

            joinwithheaders = joinwithheaders & text.Item(n)

            joinwithheaders = joinwithheaders & separator

        End If

    Next

    If joinwithheaders <> "" Then ' Remove last CR if not blank

        joinwithheaders = Left(joinwithheaders, Len(joinwithheaders) - Len(separator))

    End If

End Function

For all three examples, please see the attached Excel file below.