Collate data from Duplicates into new Column

[subz-zero]

Hi

was wondering if it was possible to collate all duplicate value into new column

before

colA colB
1 55
1 44
1 44
2 33
3 3
4 22
4 2
5 55

after would become

colA column C
1 55,44
2 33
3 3
4 22,2
5 55

so its displays unique values from COLA and the combine the results into new column eiter seperated by a comma or blank space

hope this makes sense

thanks in advance

 

[MickG]

Try this for results in columns "D & E".

[COLOR="Navy"]Sub[/COLOR] MG06Nov57
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object, K [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        Dic.Add Dn.Value, CreateObject("scripting.dictionary")
         Dic(Dn.Value).Add Dn.Offset(, 1).Value, Nothing
    [COLOR="Navy"]ElseIf[/COLOR] Not Dic(Dn.Value).exists(Dn.Offset(, 1).Value) [COLOR="Navy"]Then[/COLOR]
        Dic(Dn.Value).Add Dn.Offset(, 1).Value, Nothing
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
 c = 1
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] Dic.keys
        c = c + 1
        Cells(c, "D") = K
        Cells(c, "E") = Join(Dic(K).keys(), ", ")
    [COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[subz-zero]

Many thanks

worked a treat

thanks for your help again

 

[sandy666]

another way

PowerQuery

colA	colB		colA	colB
1​	55​		1​	55,44
1​	44​		2​	33
1​	44​		3​	3
2​	33​		4​	22,2
3​	3​		5​	55
4​	22​
4​	2​
5​	55​

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"colA"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "colB", each List.Distinct(Table.Column([Count],"colB"))),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"colB", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    #"Extracted Values"[/SIZE]