concatenate multiple

Question

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 assigned to them in column B. I need to shorten that down to one cell per user but to keep all of the data in column B. Is there anyway of Doing this? I thought about concatenating column B so everything will be in one cell but I can't seem to work out how as when I sort Column A to remove duplicates it will also remove data from column B (lots of duplicates but need to keep them for each user)Any help would be great 🙂

charla74 · Answer

Jord4&nbsp;&nbsp;You can create a user defined function for this per&nbsp;https://www.extendoffice.com/documents/excel/2706-excel-vlookup-return-multiple-values-in-one-cell.html&nbsp; (crucial part shown below):&nbsp;1. Activate your worksheet which you want to vlookup multiple values into one cell.2. Hold down the&nbsp;ALT + F11&nbsp;keys, and it opens the&nbsp;Microsoft Visual Basic for Applications window.3. Click&nbsp;Insert&nbsp;&gt;&nbsp;Module, and paste the following code in the&nbsp;Module Window.VBA code: Vlookup to return multiple values in one cell123456789101112Function MYVLOOKUP(pValue&nbsp;As String, pWorkRng&nbsp;As Range, pIndex&nbsp;As Long)'Update 20150310Dim rng&nbsp;As RangeDim xResult&nbsp;As StringxResult =&nbsp;""For Each rng&nbsp;In pWorkRng&nbsp;&nbsp;&nbsp;&nbsp;If rng = pValue&nbsp;Then&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;xResult = xResult &amp;&nbsp;" " &amp; rng.Offset(0, pIndex - 1)&nbsp;&nbsp;&nbsp;&nbsp;End IfNextMYVLOOKUP = xResultEnd Function

jord4 · Answer

Thanks!

sergeibaklan · Answer

Jord4&nbsp;
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

Forum Discussion

concatenate multiple

3 Replies

Resources