Hi Mandeep,

Am Thu, 3 Dec 2015 10:06:00 -0800 (PST) schrieb Mandeep Baluja:

Data should be like many rows :-
Col1 col2 col3 col4
1 a T C
1 b u c
1 c v c
1 a w c

So output should be like :-
Col1 col2 col3 col4
1 a,b,c T,u,w,w C

try:

Sub Test()
Dim varData As Variant, varOut As Variant
Dim LRow As Long, i As Long, j As Long
Dim myDic As Object

With Sheets("Sheet1")
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
varData = .Range("A1:D" & LRow)
For j = LBound(varData, 2) To UBound(varData, 2)
Set myDic = CreateObject("Scripting.Dictionary")
For i = LBound(varData) To UBound(varData)
myDic(varData(i, j)) = varData(i, j)
Next i
varOut = myDic.items
Sheets("Sheet2").Cells(1, j) = Join(varOut, ", ")
Set myDic = Nothing
Next j
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional