Expand each row n number of times.

[Cubist]

I want to expand each row of my data n number of times. In the example, each item# gets repeated 3 times.

Book1
	A	B	C	D	E	F	G	H
1	Num to repeat	3
2					Result
3	Item#	Product	Description		Item#	Product	Description
4	1	Product A	Desc A		1	Product A	Desc A
5	2	Product B	Desc B		1	Product A	Desc A
6	3	Product C	Desc C		1	Product A	Desc A
7	4	Product D	Desc D		2	Product B	Desc B
8	5	Product E	Desc E		2	Product B	Desc B
9					2	Product B	Desc B
10					3	Product C	Desc C
11					3	Product C	Desc C
12					3	Product C	Desc C
13					4	Product D	Desc D
14					4	Product D	Desc D
15					4	Product D	Desc D
16					5	Product E	Desc E
17					5	Product E	Desc E
18					5	Product E	Desc E
19
Sheet2

 

[Fluff]

why didn't the TEXTSPLIT(G4#,"|") in column H in post #5 spill to the right for each row

Because it doesn't work like that, when you pass an array of values you only get the column from the split.

 

[Cubist]

Ok, good to know. Thanks.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Scott R]

D4: =SEQUENCE(COUNTA(B4:B8)*B1)
E4: =INDEX(B4:C8,ROUNDUP(D4#/B1,0),SEQUENCE(,COLUMNS(B4:C4)))

 

[Dave Patton]

You may find a thread in the Lambda section interesting.

www.mrexcel.com/board/threads/repeatbynumber.1216836/page-6#posts

 

[Cubist]

Thanks, Dave. That's quite clever using SCAN.

 

[Cubist]

I was able to finish out post #5. Probably not the most efficient but just wanted the satisfaction knowing that it's possible.

SplitNameEmail.xlsx
	A	B	C	D	E	F	G
1	Num to repeat	3
2
3	Item#	Product	Description		Result
4	1	Product A	Desc A		1	Product A	Desc A
5	2	Product B	Desc B		1	Product A	Desc A
6	3	Product C	Desc C		1	Product A	Desc A
7	4	Product D	Desc D		2	Product B	Desc B
8	5	Product E	Desc E		2	Product B	Desc B
9					2	Product B	Desc B
10					3	Product C	Desc C
11					3	Product C	Desc C
12					3	Product C	Desc C
13					4	Product D	Desc D
14					4	Product D	Desc D
15					4	Product D	Desc D
16					5	Product E	Desc E
17					5	Product E	Desc E
18					5	Product E	Desc E
Sheet5

Cell Formulas
Range	Formula
E4:G18	E4	=LET(t,TEXTBEFORE((TOCOL(BYROW(A4:C8,LAMBDA(br,TEXTJOIN("|",TRUE,br)))&"*"&SEQUENCE(,B1))),"*"),TEXTSPLIT(TEXTJOIN(",",,t),"|",","))
Dynamic array formulas.