concatenate or merge cells for every change in a non-blank cell

[natforde]

Hi - first time poster here! any help greatly appreciated!
I am trying to use excel as a quick way to format a transcript and either concatenate or merge cells to aggregate comments by a speaker, please see below example of data and my first ideas at the logic of trying to resolve:

Column A	Column B	Column c = the output
natalie	comment 1
	comment 2	comment 1comment 2
other	comment 3
	comment 4	comment 3comment 4
natalie	comment 5	comment 5
other	comment 6	comment 6

herei thought i could try and make a colum (colum C) so that if cell a2 is not blank then concatenate from C2 to another cell reference (but here I need a reference for the cell up to the next non blank cell.)

Ideally i would like the final data to look like this:

Column A	Column c (merged for each cell where speaker is natalie)
natalie	comment 1comment 2
other	comment 3comment 4
natalie	comment 5
other	comment 6

does anyone have any suggestions please? happy with formulas or i could try and run VBA but its been a few years - maybe i need a loop in VBA?

 

[natforde]

i have found a similar problem but it doesnt seem to work so not sure why?

Column A	Column B	Column c = the output	trial	output
natalie	comment 1		=IF(A2="","",TEXTJOIN(", ",1,B2:INDEX(B:B,IFERROR(MATCH(A2+1,A:A,0)-1,MATCH("zzz",B:B)))))	comment 1, comment 2, comment 3, comment 4, comment 5, comment 6
	comment 2	comment 1comment 2
other	comment 3		=IF(A4="","",TEXTJOIN(", ",1,B4:INDEX(B:B,IFERROR(MATCH(A4+1,A:A,0)-1,MATCH("zzz",B:B)))))	comment 3, comment 4, comment 5, comment 6
	comment 4	comment 3comment 4
natalie	comment 5	comment 5	=IF(A6="","",TEXTJOIN(", ",1,B6:INDEX(B:B,IFERROR(MATCH(A6+1,A:A,0)-1,MATCH("zzz",B:B)))))	comment 5, comment 6
other	comment 6	comment 6	=IF(A7="","",TEXTJOIN(", ",1,B7:INDEX(B:B,IFERROR(MATCH(A7+1,A:A,0)-1,MATCH("zzz",B:B)))))	comment 6

 

[Peter_SSs]

Welcome to the MrExcel board!

Would this be any use? (So long as the list/comments are not too big)

21 08 26.xlsm
	A	B	C
1	Name	Comment	Comments
2	natalie	comment 1	comment 1comment 2
3		comment 2
4	other	comment 3	comment 3comment 4
5		comment 4
6	natalie	comment 5	comment 5
7	other	comment 6	comment 6
8
Concat Comments

Cell Formulas
Range	Formula
C2:C7	C2	=IF(A2="","",LET(t,CONCAT(B2:B$8),LEFT(t,LEN(t)-LEN(CONCAT(C3:C$8)))))

 

[natforde]

Welcome to the MrExcel board!

Would this be any use?

21 08 26.xlsm
	A	B	C
1	Name	Comment	Comments
2	natalie	comment 1	comment 1comment 2
3		comment 2
4	other	comment 3	comment 3comment 4
5		comment 4
6	natalie	comment 5	comment 5
7	other	comment 6	comment 6
8
Sheet2 (2)

Cell Formulas
Range	Formula
C2:C7	C2	=IF(A2="","",LET(t,CONCAT(B2:B$8),LEFT(t,LEN(t)-LEN(CONCAT(C3:C$8)))))

perfect thank you so much!! this forum is brilliant!

 

[steve the fish]

Try this perhaps?

=IF(A2<>"",IFERROR(TEXTJOIN(",",TRUE,INDEX(B2:INDEX(B2:$B$100,MATCH(FALSE,ISBLANK(A3:$A$1000),0)),0)),B2),"")

 

[steve the fish]

Oh sorry didnt see the reply before i posted

 

[natforde]

perfect thank you so much!! this forum is brilliant!

Try this perhaps?

=IF(A2<>"",IFERROR(TEXTJOIN(",",TRUE,INDEX(B2:INDEX(B2:$B$100,MATCH(FALSE,ISBLANK(A3:$A$1000),0)),0)),B2),"")

thank you - i am just trying it on real data and i have spill error so not sure what is happening - maybe too many characters for one cell?

 

[natforde]

fixed it thank you!

 

[Peter_SSs]

perfect thank you so much!!

You're welcome. Thanks for the follow-up.

Try this perhaps?

=IF(A2<>"",IFERROR(TEXTJOIN(",",TRUE,INDEX(B2:INDEX(B2:$B$100,MATCH(FALSE,ISBLANK(A3:$A$1000),0)),0)),B2),"")

@steve the fish
Note that this formula fails if the last name has more than one comment - see column D below.
Also not sure if you meant to use 100 rows in one spot and 1000 rows in another?
Anyway, correction suggested in column E.

21 08 26.xlsm
	A	B		D	E
1	Name	Comment
2	natalie	comment 1		comment 1,comment 2	comment 1,comment 2
3		comment 2
4	other	comment 3		comment 3,comment 4	comment 3,comment 4
5		comment 4
6	natalie	comment 5		comment 5	comment 5
7	other	comment 6		comment 6	comment 6,comment 7,comment 8
8		comment 7
9		comment 8
10
Concat Comments

Cell Formulas
Range	Formula
D2:D9	D2	=IF(A2<>"",IFERROR(TEXTJOIN(",",TRUE,INDEX(B2:INDEX(B2:$B$100,MATCH(FALSE,ISBLANK(A3:$A$1000),0)),0)),B2),"")
E2:E9	E2	=IF(A2<>"",TEXTJOIN(",",TRUE,INDEX(B2:INDEX(B2:$B$100,IFNA(MATCH(FALSE,ISBLANK(A3:$A$100),0),ROWS(B2:B$100))),0)),"")

 

[steve the fish]

Good point peter, hadnt thought of that.