How to concatenate list entries with the same ID into one cell?

[ganguro]

Hi excel family!

need your help.

this is the situation I face in Excel:

Cell A is a non-unique key field.
Cell B is the assigned value.

Cell A_______Cell B
AAA_________TMPL
AAA_________TMPS
AAA_________TMPO
BBB_________TMPS
CCC_________TMPS
CCC_________TMPO
DDD_________TMPL
DDD_________TMPS
DDD_________TMPO
DDD_________TMPX
EEE__________TMPL
EEE__________TMPS


This is what I want to achieve:


Cell A_______Cell B
AAA_________TMPL + TMPS + TMPO
BBB_________TMPS
CCC_________TMPS + TMPO
DDD_________TMPL + TMPS + TMPO
EEE__________TMPL + TMPS

I want to concatenate the list entries into one cell and separate them with a + or an & sign.

How can I achieve this?

Thx a lot in advance!

 

[YasserKhalil]

Suppose you have headers in A1:B1
Try this code

Sub Demo()
    Dim a, d As Object, i As Long

    With ActiveSheet
        a = .Range("A2:B" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
        Set d = CreateObject("Scripting.Dictionary")

        For i = LBound(a) To UBound(a)
            If Not d.Exists(a(i, 1)) Then d(a(i, 1)) = a(i, 2) Else d(a(i, 1)) = d(a(i, 1)) & " + " & a(i, 2)
        Next i
   
        With .Range("E1")
            .Resize(1, 2).Value = Array("Header1", "Header2")
            .Offset(1).Resize(d.Count).Value = Application.Transpose(d.Keys)
            .Offset(1, 1).Resize(d.Count).Value = Application.Transpose(d.Items)
        End With
    End With
End Sub

 

[ganguro]

thx buddy Yasser, will try. very much appreciated!

 

[sandy666]

another way with Power Query (Get&Transform)

Cell A	Cell B		Cell A	List
AAA	TMPL		AAA	TMPL & TMPS & TMPO
AAA	TMPS		BBB	TMPS
AAA	TMPO		CCC	TMPS & TMPO
BBB	TMPS		DDD	TMPL & TMPS & TMPO & TMPX
CCC	TMPS		EEE	TMPL & TMPS
CCC	TMPO
DDD	TMPL
DDD	TMPS
DDD	TMPO
DDD	TMPX
EEE	TMPL
EEE	TMPS

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    List = Table.AddColumn(Table.Group(Source, {"Cell A"}, {{"Count", each _, type table}}), "List", each Table.Column([Count],"Cell B")),
    Extract = Table.TransformColumns(List, {"List", each Text.Combine(List.Transform(_, Text.From), " & "), type text})
in
    Extract

 

[Peter_SSs]

.. or a worksheet formula approach (if you have the TEXTJOIN function)
D2 copied down as far as you might ever need.
E2 is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down as far as D2 was.

Book1
	A	B	C	D	E
1	Key	Value		Key	Values
2	AAA	TMPL		AAA	TMPL + TMPS + TMPO
3	AAA	TMPS		BBB	TMPS
4	AAA	TMPO		CCC	TMPS + TMPO
5	BBB	TMPS		DDD	TMPL + TMPS + TMPO + TMPX
6	CCC	TMPS		EEE	TMPL + TMPS
7	CCC	TMPO
8	DDD	TMPL
9	DDD	TMPS
10	DDD	TMPO
11	DDD	TMPX
12	EEE	TMPL
13	EEE	TMPS
14
Concat

Cell Formulas
Range	Formula
D2:D10	D2	=IFERROR(INDEX($A$2:$A$20,MATCH(0,INDEX(COUNTIF($D$1:D1,$A$2:$A$20)+(A$2:A$20=""),0),0)),"")
E2:E10	E2	{=IF(D2="","",TEXTJOIN(" + ",1,IF(A$2:A$20=D2,B$2:B$20,"")))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.