Help me consolidate excel text data

One way:

Public Function ConcatAll(ByVal varData As Variant, Optional ByVal sDelimiter As String = vbNullString, Optional ByVal bUnique As Boolean = False) As String
   
    Dim DataIndex As Variant
    Dim strResult As String
    
    If IsArray(varData) _
    Or TypeOf varData Is Range _
    Or TypeOf varData Is Collection Then
        
        For Each DataIndex In varData
            If Len(DataIndex) > 0 Then
                 If bUnique = True Then
                      If InStr(1, "||" & strResult & "||", "||" & DataIndex & "||", vbTextCompare) = 0 Then
                       trResult = strResult & "||" & DataIndex
                    End If
                Else
                    strResult = strResult & "||" & DataIndex
                End If
            End If
        Next DataIndex
        
        strResult = Replace(Mid(strResult, 3), "||", sDelimiter)
        
    Else
        strResult = varData
    End If
    
    ConcatAll = strResult
    
End Function

How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


Then... In I11, copied across and down:
=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$10)/(($A$2:$A$10<>"")*(COUNTIF($I$10:$I10,$A$2:$A$10)=0)),1)),"")

and in J11, an array formula, copied down:
=IF(I11="","",concatall(IF($A$2:$A$10=I11,$C$2:$C$10&" ("&$D$2:$D$10&")",""),CHAR(10)))

Don't forget to wrap text in column J, or manually set row width to be deep enough.

Array formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...