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]

How about

=HSTACK(TOCOL(IF(SEQUENCE(,B1),A4:A8)),TOCOL(IF(SEQUENCE(,B1),B4:B8)),TOCOL(IF(SEQUENCE(,B1),C4:C8)))

 

[Cubist]

Hi Fluff,

This works for the sample data. Sometimes I have more columns, in theory, I could repeat the TOCOL that many times, but wondering if there's a more concise syntax.

 

[Fluff]

How about

=LET(d,FILTER(A4:C100,A4:A100<>""),r,ROWS(d),s,SEQUENCE(r*B1,,0),INDEX(d,INT(s/B1)+1,SEQUENCE(,COLUMNS(d))))

 

[Cubist]

I've been playing around and getting close, but the Result isn't as expected. I have the steps laid out below easier to follow.
Edit: Posted before seeing your reply #4.

Book1 (version 2).xlsb
	A	B	C	D	E	F	G	H
1	Num to repeat	3
2
3	Item#	Product	Description		TextJoin By "|"	Expand with TOCOL	TextBefore *	Result
4	1	Product A	Desc A		1|Product A|Desc A	1|Product A|Desc A*1	1|Product A|Desc A	1
5	2	Product B	Desc B		2|Product B|Desc B	1|Product A|Desc A*2	1|Product A|Desc A	1
6	3	Product C	Desc C		3|Product C|Desc C	1|Product A|Desc A*3	1|Product A|Desc A	1
7	4	Product D	Desc D		4|Product D|Desc D	2|Product B|Desc B*1	2|Product B|Desc B	2
8	5	Product E	Desc E		5|Product E|Desc E	2|Product B|Desc B*2	2|Product B|Desc B	2
9						2|Product B|Desc B*3	2|Product B|Desc B	2
10						3|Product C|Desc C*1	3|Product C|Desc C	3
11						3|Product C|Desc C*2	3|Product C|Desc C	3
12						3|Product C|Desc C*3	3|Product C|Desc C	3
13						4|Product D|Desc D*1	4|Product D|Desc D	4
14						4|Product D|Desc D*2	4|Product D|Desc D	4
15						4|Product D|Desc D*3	4|Product D|Desc D	4
16						5|Product E|Desc E*1	5|Product E|Desc E	5
17						5|Product E|Desc E*2	5|Product E|Desc E	5
18						5|Product E|Desc E*3	5|Product E|Desc E	5
19
Sheet1

Cell Formulas
Range	Formula
F4:F18	F4	=(TOCOL(E4:E8&"*"&SEQUENCE(,B1)))
G4:G18	G4	=TEXTBEFORE(F4#,"*")
H4:H18	H4	=TEXTSPLIT(G4#,"|")
E4:E8	E4	=TEXTJOIN("|",TRUE,A4:C4)
Dynamic array formulas.

 

[Cubist]

How about

=LET(d,FILTER(A4:C100,A4:A100<>""),r,ROWS(d),s,SEQUENCE(r*B1,,0),INDEX(d,INT(s/B1)+1,SEQUENCE(,COLUMNS(d))))

This works!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[HighAndWilder]

Or a VBA option that allows you to select the data and the destination cell.

Private Sub subRepeatRows()
Dim rng As Range
Dim i As Integer
Dim ii As Integer
Dim intRow As Integer
Dim rngDest As Range
Dim r As Range

  ActiveWorkbook.Save

  On Error Resume Next
  
  Set rng = Application.InputBox( _
    Title:="Range selection.", _
    Prompt:="Select rows to repeat.", _
    Type:=8)
  
  On Error GoTo 0
  
  If rng Is Nothing Then
    Exit Sub
  End If
  
  On Error Resume Next
  
  Set rngDest = Application.InputBox( _
    Title:="Cell selection.", _
    Prompt:="Select destination cell (top left).", _
    Type:=8)
  
  On Error GoTo 0

  If rngDest Is Nothing Then
    Exit Sub
  End If
  
  intRow = rngDest.Row
 
  For Each r In rng.Columns(1).Cells
    For ii = 0 To 2
      Range(Cells(intRow + ii, rngDest.Column), Cells(intRow + ii, _
        rngDest.Column)).Resize(1, 3).Value = r.Resize(1, 3).Value
    Next ii
    intRow = intRow + 3
  Next r
  
End Sub

 

[Cubist]

Just for my learning purposes, why didn't the TEXTSPLIT(G4#,"|") in column H in post #5 spill to the right for each row? It only returned the column item#.

 

[Cubist]

@HighAndWilder Thanks for the VBA. I've test it and it works. I'll keep it in my toolbox.