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.

 

[Xlambda]

For blank or 0, we do not want to show the item.

Wow, Amazing! Considering the hipotesis, nobody expected "Repeat" vector to have text values on the first place, but anyhow, just in case, SUM got us covered and text is treated as 0's
But how should the 0's be treated then???
I am asking because if we set 0 Banana not only will get 2 Banana(s) the Cherry(s) disappear completely. Is this supposed to happen??
SUM is doing a superb job converting text to 0's but somehow fails with 0's . I am puzzled, too complex. And the arrays, why they need to be offset. It is robust to use them like that??

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Product	Repeat
2	Apple	5			=XLOOKUP(SEQUENCE(SUM(B2:B5)),SCAN(1,B1:B5,LAMBDA(a,b,SUM(a,b))),A2:A6,,-1)
3	Banana	0			Apple
4	Cherry	2			Apple
5	Strawberry	1			Apple
6					Apple
7					Apple
8					Banana
9					Banana
10					Strawberry
11
Sheet1

Cell Formulas
Range	Formula
E2	E2	=FORMULATEXT(E3)
E3:E10	E3	=XLOOKUP(SEQUENCE(SUM(B2:B5)),SCAN(1,B1:B5,LAMBDA(a,b,SUM(a,b))),A2:A6,,-1)
Dynamic array formulas.

 

[Dave Patton]

Try this version with blank or 0

Complex Formulas.xlsm
	A	B	C			F
1	Product	Repeat
2	Apple	5				Apple
3	Banana	3				Apple
4	Cherry	2				Apple
5	Strawberry	1				Apple
6						Apple
7						Banana
8						Banana
9						Banana
10						Cherry
11						Cherry
12						Strawberry
13
1b

Cell Formulas
Range	Formula
F2:F12	F2	=LOOKUP(SEQUENCE(SUM(rB)),SCAN(1,rB,LAMBDA(a,b,a+N(b))),A2:A5)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
rB	='1b'!$B$1:$B$5	F2

 

[Xlambda]

So SUM was not needed. Good. We are getting closer.
My concern is not about other data types, like text or blanks, or images in cell, or errors or whatever custom data types like online stock.....
It's not the job of inside SCAN to deal with these situations. Normally this "cleaning" should happen with a simple isnumber check of initial data.
We deal only with numbers, integers only. Simple integers. Follows a common sense hipotesis. We ignore everything else except integers, the Z numbers set.
Now, 0's and positive integers look perfectly fine, you did it, problem solved but.... ups,....what happens if numbers are negative, I think this should be also ignored. Or not?
See?? Too complicated.. N(neg.nr) stays negative. What can we do??
And why the defined named you have used in the formula (rb), still refers at a larger array B1:B5 . and not B2:B5 Can you solve it? It has an extra cell.. Has to look nice and robust.

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Product	Repeat
2	Apple	5		=LOOKUP(SEQUENCE(SUM(B1:B5)),SCAN(1,B1:B5,LAMBDA(a,b,a+N(b))),A2:A5)
3	Banana	-3		Apple
4	Cherry	2		Apple
5	Strawberry	1		Cherry
6				Cherry
7				Strawberry
8
9				3 apples missing
10
Sheet1

Cell Formulas
Range	Formula
D2	D2	=FORMULATEXT(D3)
D3:D7	D3	=LOOKUP(SEQUENCE(SUM(B1:B5)),SCAN(1,B1:B5,LAMBDA(a,b,a+N(b))),A2:A5)
Dynamic array formulas.

 

[lrobbo314]

Might not be pretty, but it works.

Book1
	A	B	C	D
1	Product	Repeat		FX
2	Apple	5		Apple
3	Banana	-3		Apple
4	Cherry	2		Apple
5	Strawberry	1		Apple
6				Apple
7				Cherry
8				Cherry
9				Strawberry
Sheet3

Cell Formulas
Range	Formula
D2:D9	D2	=LET(m,IF(B2:B5<1,0,B2:B5),LOOKUP(SEQUENCE(SUM(m)),SCAN(1,VSTACK("",m),LAMBDA(a,b,a+N(b))),A2:A5))
Dynamic array formulas.

 

[Dave Patton]

Very good! I created a Lambda function and posted it below.

Complex Formulas.xlsm
	A	B	C	D
1	Product	Repeat
2	Apple	5		Apple
3	Banana	3		Apple
4	Cherry	2		Apple
5	Strawberry	1		Apple
6				Apple
7				Banana
8				Banana
9				Banana
10				Cherry
11				Cherry
12				Strawberry
13
1b

Cell Formulas
Range	Formula
D2:D12	D2	=RepeatByNumber(A2:A5,B2:B5)
Dynamic array formulas.

Lambda Functions
Name	Formula
RepeatByNumber	=LAMBDA(rProduct,rRepeat,LET(rB,rRepeat,rA,rProduct,m,IF(rB<1,0,rB),LOOKUP(SEQUENCE(SUM(m)),SCAN(1,VSTACK("",m),LAMBDA(a,b,a+N(b))),rA)))

 

[lrobbo314]

Just for fun. Here is a way to do it using Power Query.

EXCEL
	A	B	C	D
1	Product	Repeat		RepeatByNumber
2	Apple	5		Apple
3	Banana	-3		Apple
4	Cherry	2		Apple
5	Strawberry	1		Apple
6				Apple
7				Cherry
8				Cherry
9				Strawberry
Sheet4

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    toList = List.Accumulate(
        Table.TransformRows(Source, each _),
        {},
        (s,c)=> if c[Repeat] > 0 then List.Combine({s,List.Repeat({c[Product]},c[Repeat])}) else s
    )
in
    toList

 

[Xlambda]

Super great formula/function guys. All problems were finally solved. Great jobs. Thank you for your support.
I also have an alternative lambda if I might. I was so lucky because natively has no problems with numbers <1. Pure luck.
Tell me if you like it.
Does not need any of these: XLOOKUP /LOOKUP, or SCAN, does not use enigmatic offset arrays (still did not get an answer for that)
Deals with 0's, negative numbers and is crazy fast, tested for 1M rows , for fun.

=LAMBDA(a,r,INDEX(a,TOCOL(IF(r>=SEQUENCE(,MAX(r)),SEQUENCE(ROWS(r)),NA()),2)))

a: prod array (no offset needed)
r: repeat array (no offset one row up or down needed)
Note: If we suspect other data types can interfere r can be inputed as if(isnumber(r),r,0)

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Product	Repeat
2	Apple	5		=LAMBDA(a,r,INDEX(a,TOCOL(IF(r>=SEQUENCE(,MAX(r)),SEQUENCE(ROWS(r)),NA()),2)))(A2:A8,B2:B8)
3	Banana	-3		Apple
4	Cherry	2		Apple		Or a simple formula
5	Strawberry	0		Apple		=LET(r,B2:B8,INDEX(A2:A8,TOCOL(IF(r>=SEQUENCE(,MAX(r)),SEQUENCE(ROWS(r)),NA()),2)))
6	Melon	4		Apple		Apple
7	Grapes	3		Apple		Apple
8	Lemon	2		Cherry		Apple
9				Cherry		Apple
10				Melon		Apple
11				Melon		Cherry
12				Melon		Cherry
13				Melon		Melon
14				Grapes		Melon
15				Grapes		Melon
16				Grapes		Melon
17				Lemon		Grapes
18				Lemon		Grapes
19						Grapes
20						Lemon
21						Lemon
22
Sheet1

Cell Formulas
Range	Formula
D2,F5	D2	=FORMULATEXT(D3)
D3:D18	D3	=LAMBDA(a,r,INDEX(a,TOCOL(IF(r>=SEQUENCE(,MAX(r)),SEQUENCE(ROWS(r)),NA()),2)))(A2:A8,B2:B8)
F6:F21	F6	=LET(r,B2:B8,INDEX(A2:A8,TOCOL(IF(r>=SEQUENCE(,MAX(r)),SEQUENCE(ROWS(r)),NA()),2)))
Dynamic array formulas.

 

[lrobbo314]

For a little bit more fun... This is how I did it using Python.

EXCEL
	A	B	C	D
1	Product	Repeat		Python
2	Apple	5		Apple
3	Banana	-3		Apple
4	Cherry	2		Apple
5	Strawberry	1		Apple
6				Apple
7				Cherry
8				Cherry
9				Strawberry
Sheet1

Cell Formulas
Range	Formula
D2:D9	D2	=PY("a = xl(""A2:B5"") [z for zs in [[x]*y for x,y in zip(a[0],a[1])] for z in zs]",0)
Dynamic array formulas.

a = xl("A2:B5")
[z for zs in [[x]*y for x,y in zip(a[0],a[1])] for z in zs]

 

[Dave Patton]

Hello lrobbo314

To run Python, what version of Excel is required.

I am just curious.

 

[lrobbo314]

Hello lrobbo314

To run Python, what version of Excel is required.

I am just curious.

You have to be signed up for the Excel 365 Insiders Beta thing. If you go to your account settings I think you can find it.

I've found Python in Excel to be kind of weird and cumbersome. Easier to load a csv directly into Python and go from there.

And, I heard that Microsoft is going to charge for the Python option once they really roll it out.