Forum Discussion

Jord4's avatar
Jord4
Copper Contributor
Mar 11, 2020

concatenate multiple

Hi guys, So I have a couple rows of data, column A has users populated and column B has things assigned to them. In column A the users are listed out various times depending on what they have assig...
excel
Formulas and Functions
Macros and VBA
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Mar 11, 2020

Jord4 

If you mean something like this

few other variants

1) with dynamic array Excel

A:
=UNIQUE(B3:B11)

B:
=TEXTJOIN(", ",1,FILTER($C$3:$C$11,$B$3:$B$11=E3))

2) more traditional way

A:
=IFERROR(INDEX($B$3:$B$11,AGGREGATE(15,6,1/(COUNTIF($H$2:$H2,$B$3:$B$11)=0)*(ROW($B$3:$B$11)-ROW($B$2)),1)),"")

B:
=TEXTJOIN(", ",1,IF($B$3:$B$11=$H3,$C$3:$C$11,""))

and drag them down

3) Power Query if we name source as Range

let
    Source = Excel.CurrentWorkbook(),
    FilterRange = Table.SelectRows(Source, each ([Name] = "Range")),
    Range = FilterRange{[Name="Range"]}[Content],
    PromoteHeaders = Table.PromoteHeaders(Range, [PromoteAllScalars=true]),
    GroupA = Table.Group(PromoteHeaders, {"A"}, {{"B", each Text.Combine(_[B],", ")}})
in
    GroupA

Resources