Formula to text join up to 10 rows and two columns stoping when the next lot of data starts?

[tonywatsonhelp]

Hi Everyone,

I have a very old supplier who send me information on multiple lines,
to use this data i need to combine it all together,

I was using the text join formula which worked great but now the number of rows is not consistent so i need to adjust it or use a different method.

The formula i used was this "=TEXTJOIN(", ",TRUE,C2,C3,C4,C5,C6,C7,C8,C9,C10,C11,C12,D2,D3,D4,D5,D6,D7,D8,D9,D10,D11,D12)"
Now this worked great when i could rely on the products being 10 rows but now its changed so what i need is this:

Column A holds Product Names
Each product names Info is spread over multiple rows in columns B and C
I need to combine all the data in Columns B and C into D for as many rows as belong to that product
So

A2 is Product A
product B will be down column A somewhere lets say A7 so I need to combine all of B2 to C6 (going column b first then column C.

any ideas if and how this can be done?

Thanks

Tony

 

[Fluff]

How about

=TEXTJOIN(", ",,IF(A2:A100=A2,B2:C100,""))

 

[Anthony47]

In case that @Fluff 's formula (see above) doesn't fix the request try this much longer one:

=IF(A2<>"",TEXTJOIN(",",TRUE,TEXTJOIN(",",TRUE,OFFSET(A2,0,1,ABS(MIN(IF(A3:A100<>"",ROW(A3:A100),""))-ROW()),1)),TEXTJOIN(",",TRUE,OFFSET(A2,0,2,ABS(MIN(IF(A3:A100<>"",ROW(A3:A100),""))-ROW()),1))),"")

Set it in D2 then copy down as necessary

Bye

 

[Peter_SSs]

Possibly this?

21 10 25.xlsm
	A	B	C	D	E	F
1
2	Prod1	DataB2	DataE2		Prod1	DataB2,DataB3,DataB4,DataE2,DataE3,DataE4
3		DataB3	DataE3		Prod2	DataB5,DataB6,DataE5,DataE6
4		DataB4	DataE4		Prod3	DataB7,DataB8,DataB9,DataE7,DataE8,DataE9
5	Prod2	DataB5	DataE5		Prod4	DataB10,DataB11,DataB12,DataB13,DataE10,DataE11,DataE12,DataE13
6		DataB6	DataE6		Prod5	DataB14,DataB15,DataE14,DataE15
7	Prod3	DataB7	DataE7		Prod6	DataB16,DataB17,DataE16,DataE17
8		DataB8	DataE8
9		DataB9	DataE9
10	Prod4	DataB10	DataE10
11		DataB11	DataE11
12		DataB12	DataE12
13		DataB13	DataE13
14	Prod5	DataB14	DataE14
15		DataB15	DataE15
16	Prod6	DataB16	DataE16
17		DataB17	DataE17
18
TJ

Cell Formulas
Range	Formula
E2:E7	E2	=FILTER(A2:A20,A2:A20<>"")
F2:F9	F2	=IF(E2="","",LET(a,A$2:A$100,b,B$2:B$100,c,C$2:C$100,s,MATCH(E2,a,0),e,IFERROR(MATCH(E3,a,0)-1,ROWS(a)),TEXTJOIN(",",1,INDEX(b,s):INDEX(b,e),INDEX(c,s):INDEX(c,e))))
Dynamic array formulas.

 

[Peter_SSs]

.. or a tiny bit shorter

21 10 25.xlsm
	A	B	C	D	E	F
1
2	Prod1	DataB2	DataE2		Prod1	DataB2,DataB3,DataB4,DataE2,DataE3,DataE4
3		DataB3	DataE3		Prod2	DataB5,DataB6,DataE5,DataE6
4		DataB4	DataE4		Prod3	DataB7,DataB8,DataB9,DataE7,DataE8,DataE9
5	Prod2	DataB5	DataE5		Prod4	DataB10,DataB11,DataB12,DataB13,DataE10,DataE11,DataE12,DataE13
6		DataB6	DataE6		Prod5	DataB14,DataB15,DataE14,DataE15
7	Prod3	DataB7	DataE7		Prod6	DataB16,DataB17,DataE16,DataE17
8		DataB8	DataE8
9		DataB9	DataE9
10	Prod4	DataB10	DataE10
11		DataB11	DataE11
12		DataB12	DataE12
13		DataB13	DataE13
14	Prod5	DataB14	DataE14
15		DataB15	DataE15
16	Prod6	DataB16	DataE16
17		DataB17	DataE17
18
TJ (2)

Cell Formulas
Range	Formula
E2:E7	E2	=FILTER(A2:A20,A2:A20<>"")
F2:F9	F2	=IF(E2="","",LET(a,A$2:A$100,bc,B$2:C$100,TEXTJOIN(",",1,TRANSPOSE(INDEX(bc,MATCH(E2,a,0),1):INDEX(bc,IFERROR(MATCH(E3,a,0)-1,ROWS(a)),2)))))
Dynamic array formulas.

 

[tonywatsonhelp]

Fluff, thanks you great idea,
Anthony very helpfull thank you for your input.
Peter, Wow what can i say, solved 100% thanks
Thank you to all of you for your help

Thanks

Tony

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Peter_SSs]

You're welcome. Glad it worked for you.