Extract unique data from column and associate data

[Exceladd1ct]

Hello, i need a VBA code that will extract the unique values from column A

and associate it's data from column B

So far i only managed to extract unique data from col A into an Array.


Thanks.

 

[sandy666]

With Power Query

Phone Brand	Phone Color		Phone Brand	Phone Color
Apple	red		Apple	red,blue,maroon,orange
Samsung	blue		Samsung	blue,red,yellow,white
Apple	blue		Huawei	black,white,red,green,blue
Apple	maroon
Huawei	black
Samsung	red
Huawei	white
Huawei	red
Apple	orange
Huawei	green
Huawei	blue
Samsung	yellow
Samsung	white

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Phone Brand"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Phone Color", each List.Distinct([Count][Phone Color])),
    Extract = Table.TransformColumns(List, {"Phone Color", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    Extract

 

[Fluff]

How about

Sub Exceladd1ct()
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         If Not .Exists(Cl.Value) Then
            .Add Cl.Value, Cl.Offset(, 1).Value
         Else
            .Item(Cl.Value) = .Item(Cl.Value) & ", " & Cl.Offset(, 1).Value
         End If
      Next Cl
      Range("E2").Resize(.Count, 2).Value = Application.Transpose(Array(.Keys, .Items))
   End With
End Sub

 

[Exceladd1ct]

Thanks. Both methods are working.
I am just curious if there is an "arrays" approach to this. Because that's what i've tried.

 

[Fluff]

You could do it with a normal array rather than a dictionary, but it would be more complicated & slower.