Depending on what version of Power Pivot you have, you may be able to use CONCATENATEX for this: https://www.mrexcel.com/excel-tips/pivot-table-with-text-in-values-area/

Velouria,  Tested your suggestion, and it works on my version of Power Pivot the way A J M Raghu would like it to work.  Just need to substitute , with ; in Mr Excel concatenatex formula.

I learn so much from everyone here.

Thanks,

Steve

Velouria said
Depending on what version of Power Pivot you have, you may be able to use CONCATENATEX for this: https://www.mrexcel.com/excel-tips/pivot-table-with-text-in-values-area/   

Thanks for responding so quickly.  However, in my system, i don't have Power Pivot. 

I think its Excel 2013.  in which i could not find "Data to Data Model" option, not did if find "Add Measures" Option

Hence, request you to plz suggest accordingly

thanks & regards

Raghu

Which version of 2013? If it's Professional Plus, or Excel standalone, you should have Power Pivot. If it's a different version, you cannot use it and you will not be able to do what you want with a pivot table, so you will want code. Please post an example workbook if you want code.

please find attached the work book 

Give this a try.

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