REPEATBYNUMBER

[smozgur]

REPEATBYNUMBER function takes a range (or table) that has the first column as the item name and the last column as the repeat number then returns a new column of rows with the item names repeated by the required number of times. (I learned the main idea from Karina Adcock, and used the TAKE function to make it work with a single range/table parameter).

EDIT: This function assumes that each product in the source data exists in the inventory (Repeat >= 1). For an alternative implementation that considers Repeat = 0 please see @Xlambda's RPTBYNR function below.

=LAMBDA(data,
    XLOOKUP(
        SEQUENCE(SUM(TAKE(data,,-1))),
        VSTACK(1, SCAN(1,TAKE(data,,-1),LAMBDA(a,b,a+b))),
        VSTACK(TAKE(data,,1),""),
        ,
        -1
    )
)

RepeatByNumber
	A	B	C	D	E	F
1	Product	Repeat		Result (range param)		Result (table param)
2	Apple	5		Apple		Apple
3	Banana	4		Apple		Apple
4	Cherry	2		Apple		Apple
5	Strawberry	4		Apple		Apple
6				Apple		Apple
7				Banana		Banana
8				Banana		Banana
9				Banana		Banana
10				Banana		Banana
11				Cherry		Cherry
12				Cherry		Cherry
13				Strawberry		Strawberry
14				Strawberry		Strawberry
15				Strawberry		Strawberry
16				Strawberry		Strawberry
Sheet4

Cell Formulas
Range	Formula
D2:D16	D2	=REPEATBYNUMBER(A2:B5)
F2:F16	F2	=REPEATBYNUMBER(ProductTable)
Dynamic array formulas.

 

[smozgur]

Thanks for posting the alternative function, @st001.

(Note: I edited your post to use XLS code tags to allow readers copy and paste the function easily)

 

[Xlambda]

RWSRPT(ar) Rows Repeat function, no need of SCAN, REDUCE, VSTACK, or XLOOKUP

=LAMBDA(ar,
    LET(
        a, DROP(ar, , -1),
        n, TAKE(ar, , -1),
        INDEX(
            a,
            TOCOL(IF(n >= SEQUENCE(, MAX(n)), SEQUENCE(ROWS(a)), NA()), 2),
            SEQUENCE(, COLUMNS(a))
        )
    )
)

repeat product names.xlsx
	A	B	C	D	E	F	G	H	I	J
1
2		ar					=RWSRPT(B3:E6)
3		a	e	i	3		a	e	i
4		b	f	j	5		a	e	i
5		c	g	k	4		a	e	i
6		d	h	l	2		b	f	j
7							b	f	j
8							b	f	j
9							b	f	j
10							b	f	j
11							c	g	k
12							c	g	k
13							c	g	k
14							c	g	k
15							d	h	l
16							d	h	l
17
18
19
20		ar					=RWSRPT(B21:E24)
21		a	e	i	3		a	e	i
22		b	f	j	5		a	e	i
23		c	g	k	0		a	e	i
24		d	h	l	2		b	f	j
25							b	f	j
26							b	f	j
27							b	f	j
28							b	f	j
29							d	h	l
30							d	h	l
31
RWSRPT

Cell Formulas
Range	Formula
G2,G20	G2	=FORMULATEXT(G3)
G3:I16,G21:I30	G3	=RWSRPT(B3:E6)
Dynamic array formulas.

 

[st001]

RWSRPT(ar) Rows Repeat function, no need of SCAN, REDUCE, VSTACK, or XLOOKUP

=LAMBDA(ar,
    LET(
        a, DROP(ar, , -1),
        n, TAKE(ar, , -1),
        INDEX(
            a,
            TOCOL(IF(n >= SEQUENCE(, MAX(n)), SEQUENCE(ROWS(a)), NA()), 2),
            SEQUENCE(, COLUMNS(a))
        )
    )
)

repeat product names.xlsx
	A	B	C	D	E	F	G	H	I	J
1
2		ar					=RWSRPT(B3:E6)
3		a	e	i	3		a	e	i
4		b	f	j	5		a	e	i
5		c	g	k	4		a	e	i
6		d	h	l	2		b	f	j
7							b	f	j
8							b	f	j
9							b	f	j
10							b	f	j
11							c	g	k
12							c	g	k
13							c	g	k
14							c	g	k
15							d	h	l
16							d	h	l
17
18
19
20		ar					=RWSRPT(B21:E24)
21		a	e	i	3		a	e	i
22		b	f	j	5		a	e	i
23		c	g	k	0		a	e	i
24		d	h	l	2		b	f	j
25							b	f	j
26							b	f	j
27							b	f	j
28							b	f	j
29							d	h	l
30							d	h	l
31
RWSRPT

Cell Formulas
Range	Formula
G2,G20	G2	=FORMULATEXT(G3)
G3:I16,G21:I30	G3	=RWSRPT(B3:E6)
Dynamic array formulas.

Very impressive!

But the other one doesn't use TOCOL, MAX, SEQUENCE, TAKE, ROWS, COLUMNS, or INDEX... : )

Not sure which is faster.

 

[st001]

But I actually prefer your new method. Its very elegant!

 

[smozgur]

RWSRPT(ar) Rows Repeat function, no need of SCAN, REDUCE, VSTACK, or XLOOKUP

=LAMBDA(ar,
    LET(
        a, DROP(ar, , -1),
        n, TAKE(ar, , -1),
        INDEX(
            a,
            TOCOL(IF(n >= SEQUENCE(, MAX(n)), SEQUENCE(ROWS(a)), NA()), 2),
            SEQUENCE(, COLUMNS(a))
        )
    )
)

@Xlambda: Thanks for sharing.

Honestly, took me 15 minutes to understand it. After that, I can say this is beyond beautiful to me. Math, algorithm, and functions used in this short formula. Really good one!

 

[Xlambda]

But I actually prefer your new method. Its very elegant!

Thanks a lot!!
I love iterations, recursive or with REDUCE, but I only use them when there is no other alternative.
So, I've tested both functions, neck to neck.
Here are the results:

repeat product names.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		=RANDARRAY(5000,4,1,5,1)					calc time 35.36 sec				calc time: instant
2		above formula, c-p as values
3		down to 5000 rows					=AROWREPEAT(B4:E5003)				=RWSRPT(B4:E5003)				check rows
4		4	5	1	4		4	5	1		4	5	1		source ar
5		4	2	3	4		4	5	1		4	5	1		=ROWS(B4:E5003)
6		4	3	1	3		4	5	1		4	5	1		5000
7		3	3	1	3		4	5	1		4	5	1
8		1	1	2	2		4	2	3		4	2	3		result arrays
9		3	3	4	4		4	2	3		4	2	3		=ROWS(G4#)
10		4	1	1	1		4	2	3		4	2	3		15118
11		2	2	1	3		4	2	3		4	2	3
12		3	3	3	3		4	3	1		4	3	1		=ROWS(K4#)
13		4	4	2	3		4	3	1		4	3	1		15118
14		4	5	1	1		4	3	1		4	3	1
15		3	2	2	1		3	3	1		3	3	1
16		3	2	2	5		3	3	1		3	3	1
17		5	2	2	4		3	3	1		3	3	1
18		4	3	4	4		1	1	2		1	1	2
19		3	4	1	4		1	1	2		1	1	2
20		2	5	1	4		3	3	4		3	3	4
21		4	3	4	1		3	3	4		3	3	4
22		5	1	1	1		3	3	4		3	3	4
23		5	5	4	1		3	3	4		3	3	4
24		5	2	4	2		4	1	1		4	1	1
25		3	5	2	2		2	2	1		2	2	1
26		2	1	2	2		2	2	1		2	2	1
27		5	4	2	5		2	2	1		2	2	1
28		4	3	4	3		3	3	3		3	3	3
29		1	4	5	2		3	3	3		3	3	3
30		2	5	4	3		3	3	3		3	3	3
31		3	1	1	1		4	4	2		4	4	2
32		1	5	1	4		4	4	2		4	4	2
33		2	4	5	5		4	4	2		4	4	2
34		1	5	2	2		4	5	1		4	5	1
35		2	4	1	5		3	2	2		3	2	2
36		5	3	2	5		3	2	2		3	2	2
37		1	4	3	1		3	2	2		3	2	2
38		1	5	5	2		3	2	2		3	2	2
39		3	4	5	2		3	2	2		3	2	2
40		2	4	4	1		3	2	2		3	2	2
41		4	1	1	5		5	2	2		5	2	2
42		4	5	2	4		5	2	2		5	2	2
43		4	5	4	1		5	2	2		5	2	2
44		1	4	4	3		5	2	2		5	2	2
45		3	3	5	2		4	3	4		4	3	4
46		5	3	1	4		4	3	4		4	3	4
47		1	4	2	3		4	3	4		4	3	4
48		3	2	1	4		4	3	4		4	3	4
49		4	5	1	2		3	4	1		3	4	1
50		1	5	5	5		3	4	1		3	4	1
51		4	4	2	4		3	4	1		3	4	1
52		5	2	1	4		3	4	1		3	4	1
53		1	5	5	5		2	5	1		2	5	1
54		5	4	3	4		2	5	1		2	5	1
55		1	2	3	1		2	5	1		2	5	1
56		3	4	4	1		2	5	1		2	5	1
57		3	5	4	3		4	3	4		4	3	4
58		1	3	3	4		5	1	1		5	1	1
59		5	1	5	3		5	5	4		5	5	4
60		4	4	5	4		5	2	4		5	2	4
61		2	4	2	2		5	2	4		5	2	4
62		5	1	2	4		3	5	2		3	5	2
63		5	2	3	5		3	5	2		3	5	2
64		1	5	2	2		2	1	2		2	1	2
65		5	2	5	4		2	1	2		2	1	2
66		1	3	3	2		5	4	2		5	4	2
67		4	4	2	1		5	4	2		5	4	2
68		4	4	5	1		5	4	2		5	4	2
69		3	4	1	5		5	4	2		5	4	2
70		5	1	5	5		5	4	2		5	4	2
Sheet10

Cell Formulas
Range	Formula
G3,K3,O12,O9,O5	G3	=FORMULATEXT(G4)
G4:I15121	G4	=AROWREPEAT(B4:E5003)
K4:M15121	K4	=RWSRPT(B4:E5003)
O6	O6	=ROWS(B4:E5003)
O10	O10	=ROWS(G4#)
O13	O13	=ROWS(K4#)
Dynamic array formulas.

 

[Xlambda]

@Xlambda: Thanks for sharing.

Honestly, took me 15 minutes to understand it. After that, I can say this is beyond beautiful to me. Math, algorithm, and functions used in this short formula. Really good one!

Thank you!! Glad you liked it!!

 

[st001]

Thanks Xlambda! And thanks for doing this testing!

Lesson learnt!
The simpler the better, and it's worth doing some tests to see what the most optimum is. Marco and Alberto do this a lot in the 'DAX' world.
I wonder if Microsoft plan on fixing the XLOOKUP bug, and giving us even more functions... : )

 

[lrobbo314]

Very cool. I have LET and LAMBDA, but don't have VSTACK, TOCOL, TAKE, or DROP.

Was just curious as to how this all was working. Was able to make it work with the functions I have available, even though it would be an ugly LAMBDA function.

But, it was a good learning experience and very cool to see how you did it. I'm still trying to get in the habit of using XLOOKUP, and didn't realize how cool of a function it really is. The -1 for the match_mode parameter is sweet.

Anyway, this was how I made it work.

Book1 (version 1).xlsb
	A	B	C	D
1	Product	Repeat		Apple
2	Apple	5		Apple
3	Banana	4		Apple
4	Cherry	2		Apple
5	Strawberry	4		Apple
6				Banana
7				Banana
8				Banana
9				Banana
10				Cherry
11				Cherry
12				Strawberry
13				Strawberry
14				Strawberry
15				Strawberry
Sheet2

Cell Formulas
Range	Formula
D1:D15	D1	=LET(s,SEQUENCE(SUM(B2:B5)),XLOOKUP(s,SCAN(1,B1:B5,LAMBDA(a,b,IF(ISNUMBER(b),a+b,1))),A2:A6,,-1))
Dynamic array formulas.

 

[smozgur]

Actually, I thought Power Query will complete the series, but I think it won't be fair to Excel Custom Functions. This can be easily tested in ScriptLab.
(It is very easy to deal with arrays in JavaScript.)

/**
 * @customfunction
 * @param {any[][]} rng
 * @return {any[][]}
 */
function ExploreCF(rng) {
  var result = [];
  rng.forEach((cols) => {
    for (var i = 1; i <= cols[1] ; i++) {
      result.push([cols[0]]);
    }
  });
  return result;
}