How to auto Concat data if some data has blank cells with VBA

Question

I have the following problem can anyone help using VBA / Formula I've tried it but I'm confused with what looping variation I should use for this and my excel skills are pretty much up to here :( I hope someone can help.

I want the result as in column C

Answer 1

Microsoft documentation:

Dictionary object

Range.End property (Excel)

Option Explicit
Sub Demo()
    Dim objDic As Object
    Dim i As Long, sKey As String
    Dim arrData, arrRes, lastRow As Long
    Set objDic = CreateObject("scripting.dictionary")
    lastRow = Cells(Rows.Count, 2).End(xlUp).Row
    ' Load data into an array
    arrData = Range("A1:C" & lastRow).Value
    ' Loop through data
    For i = LBound(arrData) To UBound(arrData)
        If Len(arrData(i, 1)) > 0 Then sKey = arrData(i, 1)
        ' Consolidate data by the first col
        If objDic.exists(sKey) Then
            objDic(sKey) = objDic(sKey) & arrData(i, 2)
        Else
            objDic(sKey) = arrData(i, 2)
        End If
    Next i
    ' Populate the 3rd col
    For i = LBound(arrData) To UBound(arrData)
        sKey = arrData(i, 1)
        If Len(sKey) > 0 Then
            arrData(i, 3) = objDic(sKey)
        End If
    Next i
    ' Write output to sheet
    Range("A1:C" & lastRow).Value = arrData
End Sub

---

Formula in C1

=IF(A1="","",TEXTJOIN(,,OFFSET(B1,,,IFERROR(MATCH(FALSE,ISBLANK(A2:$A$100),),ROWS(A2:$A$100)),)))