Concatenate based on cell value

[thedeadzeds]

Hi Guys

I have a table with shows comments (or notes) for each No in column A. I need to be able to get all the comments in one cell. An example of this would be the 'Notes concate' column. As you can see, No 1 as 4 comments but these appear on different rows. Is there a way to concatenate all of these comments in to one cell as per the example in Notes concate column?

Thanks

No	Ref1	Ref2	Ref3	Ref4	Ref5	Ref6	Ref7	Ref8	Ref9	Ref10	Ref11	Ref12	Ref13	Notes	Notes concate
1	ABC	24045	Red	149	0	0	0	0	13	0	0	15	15250	Comment 1	Comment 1, Comment 2, Comment 3, Comment 4
				0	0	0	0	0	0	0	0	0	0	Comment 2
				15	0	15	0	0	0	0	0	0	0	Comment 3
				15	0	0	0	0	12	0	0	15	0	Comment 4
				0	0	0	0	0	0	0	0	0	0	Comment 5
2	ABCD	24045	Green	149	0	0	0	0	13	0	0	15	15250	Comment 1	Comment 1
3	ABCDE	24045	Orange	149	0	0	0	0	13	0	0	15	15250	Comment 1	Comment 1
4	ABCDEF	24045	Blue	149	0	0	0	0	13	0	0	15	15250	Comment 1	Comment 1
5	ABCDEFG	24046	Blue	149	0	0	0	0	13	0	0	15	15250	Comment 1	Comment 1, Comment 2, Comment 3
				0	0	0	0	0	0	0	0	0	0	Comment 2
				0	0	0	0	0	0	0	0	0	0	Comment 3

 

[jasonb75]

This is how I would do it based on the example provided.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	No	Ref1	Ref2	Ref3	Ref4	Ref5	Ref6	Ref7	Ref8	Ref9	Ref10	Ref11	Ref12	Ref13	Notes	Notes concate
2	1	ABC	24045	Red	149	0	0	0	0	13	0	0	15	15250	Comment 1	Comment 1, Comment 2, Comment 3, Comment 4, Comment 5
3					0	0	0	0	0	0	0	0	0	0	Comment 2
4					15	0	15	0	0	0	0	0	0	0	Comment 3
5					15	0	0	0	0	12	0	0	15	0	Comment 4
6					0	0	0	0	0	0	0	0	0	0	Comment 5
7	2	ABCD	24045	Green	149	0	0	0	0	13	0	0	15	15250	Comment 1	Comment 1
8	3	ABCDE	24045	Orange	149	0	0	0	0	13	0	0	15	15250	Comment 1	Comment 1
9	4	ABCDEF	24045	Blue	149	0	0	0	0	13	0	0	15	15250	Comment 1	Comment 1
10	5	ABCDEFG	24046	Blue	149	0	0	0	0	13	0	0	15	15250	Comment 1	Comment 1, Comment 2, Comment 3
11					0	0	0	0	0	0	0	0	0	0	Comment 2
12					0	0	0	0	0	0	0	0	0	0	Comment 3
Sheet6

Cell Formulas
Range	Formula
P2:P12	P2	=IF(A2="","",TEXTJOIN(", ",1,O2:INDEX(O:O,IFERROR(MATCH(A2+1,A:A,0)-1,MATCH("zzz",O:O)))))