combine multiple "dynamic"rows into one cell

Solution 1:

I suggest a solution whereby you are just required to leave one blank cell at top of your data, as seen in your sample.

If you have Office 365 you can seamlessly use TEXTJOIN function. However most versions of Excel do not have this built-in function and you may be required to code one in VBA on your own. You can freely find VBA version on the internet for example one such reference is here.

Press ALT + F11 to access VBA Editor. Insert a Module and paste the below code into it.

Function TEXTJOIN1(delimiter As String, ignore_empty As Boolean, ParamArray cell_ar() As Variant)
    For Each cellrng In cell_ar
        For Each cell In cellrng
            If ignore_empty = False Then
                result = result & cell & delimiter
            Else
                If cell <> "" Then
                    result = result & cell & delimiter
                End If
            End If
        Next cell
    Next cellrng
    TEXTJOIN1 = Left(result, Len(result) - Len(delimiter))
End Function

This creates a custom VBA User Defined Function called TEXTJOIN1. It takes in 3 parameters, the Delimiter, Ignore Empty Flag & Range.

In this example sample data is in Cells B2:B14. B1 is left blank intentionally.

Put the following formula in C2 and press CTRL + SHIFT + ENTER from within the formula bar to create an Array Formula. The formula shall now be enclosed in Curly Braces to indicate that it's an array formula and Drag it down along the length of your column.

=IF(ISBLANK(B2),TEXTJOIN1(",",FALSE,OFFSET(B2,-(ROW()-MAX(IF(ISBLANK($B$1:B1),ROW($B$1:B1),0))-1),0):OFFSET(B2,-1,0)),"")

Update:

Above solution assumes that you have just one blank between set of data. However if you have more than one and if you wish that the formula should still work, use the following Array Formula instead.

=IF(AND(ISBLANK(B2),NOT(ISBLANK(OFFSET(B2,-1,0)))),TEXTJOIN1(",",TRUE,OFFSET(B2,-(ROW()-MAX(IF(ISBLANK($B$1:B1),ROW($B$1:B1),0))-1),0):OFFSET(B2,-1,0)),"")