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

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]colA[/td][td=bgcolor:#5B9BD5]colB[/td][td][/td][td=bgcolor:#70AD47]colA[/td][td=bgcolor:#70AD47]colB[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

1​

[/td][td=bgcolor:#DDEBF7]

55​

[/td][td][/td][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]55,44[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

1​

[/td][td]

44​

[/td][td][/td][td]

2​

[/td][td]33[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

1​

[/td][td=bgcolor:#DDEBF7]

44​

[/td][td][/td][td=bgcolor:#E2EFDA]

3​

[/td][td=bgcolor:#E2EFDA]3[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

2​

[/td][td]

33​

[/td][td][/td][td]

4​

[/td][td]22,2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

3​

[/td][td=bgcolor:#DDEBF7]

3​

[/td][td][/td][td=bgcolor:#E2EFDA]

5​

[/td][td=bgcolor:#E2EFDA]55[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

4​

[/td][td]

22​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

4​

[/td][td=bgcolor:#DDEBF7]

2​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

5​

[/td][td]

55​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

[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]