consolidating the data in single cell|General Excel Questions & Answers|Excel Forum|My Online Training Hub

Depending on what version of Power Pivot you have, you may be able to use CONCATENATEX for this: https://www.mrexcel.com/excel-.....lues-area/

Sunny has already given you a great answer, but since I'd done it anyway, and in case your data set isn't sorted, you could also try this:

Sub ConsolidateData()
' change first column letter as necessary
Dim FirstColumn As String
FirstColumn = "A"
' change the start row (after the header) if necessary
Dim FirstDataRow As Long
FirstDataRow = 3

Dim LastRow As Long
LastRow = Cells(Rows.Count, FirstColumn).End(xlUp).Row

Dim DataRange As Range
Set DataRange = Cells(FirstDataRow, FirstColumn).Resize(LastRow - FirstDataRow + 1, 2)

Dim DataSet
DataSet = DataRange.Value

Application.ScreenUpdating = False

DataRange.Offset(, DataRange.Columns.Count + 1).EntireColumn.Clear
Dim dic As Object
Set dic = CreateObject("Scripting.Dictionary")
Dim i As Long
For i = LBound(DataSet) To UBound(DataSet)
If Not dic.exists(DataSet(i, 1)) Then
dic(DataSet(i, 1)) = DataSet(i, 2)
Else
dic(DataSet(i, 1)) = dic(DataSet(i, 1)) & ";" & DataSet(i, 2)
End If
Next i
If dic.Count > 0 Then
With DataRange.Rows(1)
.Offset(-1, .Columns.Count + 1).Value = .Offset(-1).Value
With .Offset(, .Columns.Count + 1).Resize(dic.Count, .Columns.Count)
.Value = Application.Transpose(Array(dic.keys, dic.items))
.EntireColumn.AutoFit
End With
End With
End If

End Sub