String concatenation in multiple row

[trduy1908]

Sorry Everyone,

I have data 2 column A, B with 6 rows below:

A	B
text 1A	text 1B
	text 2B
	text 3B
text 2A	text 4B
	text 5B
text 3A	text 6B

I want to run VBA to result:

A	B
text 1A	text 1B, text 2B, text 3B
text 2A	text 4B, text 5B
text 3A	text 6B

Please give me keyword to research or solution ?
Thanks so much!

 

[lrobbo314]

Here is one way.

Book1
	A	B	C	D	E
1	text 1A	text 1B		text 1A	text 1B,text 2B,text 3B
2		text 2B		text 2A	text 4B,text 5B
3		text 3B		text 3A	text 6B
4	text 2A	text 4B
5		text 5B
6	text 3A	text 6B
Sheet1

Sub COMBO()
Dim r As Range:         Set r = Range("A1:B" & Range("B" & Rows.Count).End(xlUp).Row)
Dim AR() As Variant:    AR = r.Value2
Dim SD As Object:       Set SD = CreateObject("Scripting.Dictionary")

For i = 1 To UBound(AR)
    If i > 1 Then If AR(i, 1) = "" Then AR(i, 1) = AR(i - 1, 1)
    SD(AR(i, 1)) = Join(Array(SD(AR(i, 1)), AR(i, 2)), IIf(IsEmpty(SD(AR(i, 1))), vbNullString, ","))
Next i

Range("D1").Resize(SD.Count) = Application.Transpose(SD.keys())
Range("E1").Resize(SD.Count) = Application.Transpose(SD.items())
End Sub

 

[lrobbo314]

Could also use Power Query.

Book1
	A	B	C	D	E
1	Column1	Column2		Column1	Combine
2	text 1A	text 1B		text 1A	text 1B,text 2B,text 3B
3		text 2B		text 2A	text 4B,text 5B
4		text 3B		text 3A	text 6B
5	text 2A	text 4B
6		text 5B
7	text 3A	text 6B
Sheet2

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    FillDown = Table.FillDown(Source,{"Column1"}),
    Group = Table.Group(FillDown, {"Column1"}, {{"Combine", each Text.Combine(_[Column2],",")}})
in
    Group

 

[lrobbo314]

Or with a formula.

Excel
	A	B	C	D	E
1	text 1A	text 1B		text 1A	text 1B, text 2B, text 3B
2		text 2B		text 2A	text 4B, text 5B
3		text 3B		text 3A	text 6B
4	text 2A	text 4B
5		text 5B
6	text 3A	text 6B
Data

Cell Formulas
Range	Formula
D1:E3	D1	=LET(fd,SCAN("",A1:A6,LAMBDA(s,c,IF(c="",s,c))),u,UNIQUE(fd),HSTACK(u,BYROW(u,LAMBDA(br,TEXTJOIN(", ",,IF(br=fd,B1:B6,""))))))
Dynamic array formulas.