Concatenate of different rows data in a single cell

[Jyotirmaya]

I have data in Sheet1 in Column A & B.
In Column A I have Data, based on the Column A Data there are data in Column B, I want that in Column C those data of Column B should place in a cell by sepearated by a Comma.

Book2.xlsx
	A	B	C
1	1	a	a,b,c
2		b
3		c
4	5	a	a,b,c
5		b
6		c
7	4	a	a,b
8		b
9	5	a	a,b,c,d,e,f
10		b
11		c
12		d
13		e
14		f
15	6	a	a,b,c,d
16		b
17		c
18		d
19	5	a	a,b,c,d,e,f,g
20		b
21		c
22		d
23		e
24		f
25		g
26	8	a	a
27	9	a	a
Sheet2

Please help me with the code.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

You might also clarify whether you are looking for a (formula/vba/power query/don't care) approach.

 

[Jyotirmaya]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

You might also clarify whether you are looking for a (formula/vba/power query/don't care) approach.

Yes sir updated account details, I am using Ms Excel 2019 in Windows, I am looking for a formula/VBA.
Thanks

 

[Peter_SSs]

updated account details,

Thanks for that.

I am looking for a formula/VBA.
Thanks

Try this in C1, copied down.
Assumes data does not go down to row 100. If it does, increase the 100 values in the formula.

=IF(A1="","",TEXTJOIN(",",1,B1:INDEX(B1:B$100,IFERROR(MATCH(TRUE,A2:A$100<>"",0),ROWS(A2:A$100)))))