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]

Hi Suat, really sorry to bother, I am positive you did not read my latest posts under AFLAT thread solving same challenge using same XLOOKUP approach. You would have noticed that the VSTACKs are not needed.
The only difference there, apart of the lack of VSTACKs, is that I have used for running totals MMULT instead of SCAN (that made the formula a formula alternative free of the new functions).
=LET(p,B4:B6,n,C4:C6,r,ROWS(n ),XLOOKUP(SEQUENCE(SUM(n )),MMULT(--(SEQUENCE(r)>=SEQUENCE(,r)),n),p,,1))
Also, if one of the repeating values is 0 , your function REPEATBYNUMBERS delivers wrong results. I know that we can prepare the input array to fit our needs with FILTER or other conditions, but if works for 0's proves that the formulas we use are kind of healthy, algorithm wise.
So, for XLOOKUP approach (with SCAN):
RPTBYNR(a)

=LAMBDA(a,
    LET(
        b, TAKE(a, , -1),
        XLOOKUP(SEQUENCE(SUM(b)), SCAN(0, b, LAMBDA(v, i, v + i)), TAKE(a, , 1), , 1)
    )
)

Or an alternative, TOCOL approach:
RPTNTIMES(a)

=LAMBDA(a,
    LET(b, TAKE(a, , -1), TOCOL(IF(b >= SEQUENCE(, MAX(b)), TAKE(a, , 1), NA()), 2))
)

repeat product names.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1									if one is 0
2	Product	Repeat		=REPEATBYNUMBER(A3:B6)				Product	Repeat		=REPEATBYNUMBER(H3:I6)
3	Apple	5		Apple				Apple	5		Apple
4	Banana	4		Apple				Banana	0		Apple
5	Cherry	2		Apple				Cherry	2		Apple
6	Straberry	4		Apple				Straberry	4		Apple
7				Apple							Apple
8				Banana							Banana
9				Banana							Banana
10				Banana							Straberry
11				Banana							Straberry
12				Cherry							Straberry
13				Cherry							Straberry
14				Straberry
15				Straberry
16				Straberry
17				Straberry
18
19									if one is 0
20	Product	Repeat		=RPTBYNR(A3:B6)		=RPTNTIMES(A3:B6)		Product	Repeat		=RPTBYNR(H3:I6)		=RPTNTIMES(H3:I6)
21	Apple	5		Apple		Apple		Apple	5		Apple		Apple
22	Banana	4		Apple		Apple		Banana	0		Apple		Apple
23	Cherry	2		Apple		Apple		Cherry	2		Apple		Apple
24	Straberry	4		Apple		Apple		Straberry	4		Apple		Apple
25				Apple		Apple					Apple		Apple
26				Banana		Banana					Cherry		Cherry
27				Banana		Banana					Cherry		Cherry
28				Banana		Banana					Straberry		Straberry
29				Banana		Banana					Straberry		Straberry
30				Cherry		Cherry					Straberry		Straberry
31				Cherry		Cherry					Straberry		Straberry
32				Straberry		Straberry
33				Straberry		Straberry
34				Straberry		Straberry
35				Straberry		Straberry
36
Sheet5

Cell Formulas
Range	Formula
D2,K20,M20,D20,F20,K2	D2	=FORMULATEXT(D3)
D3:D17,K3:K13	D3	=REPEATBYNUMBER(A3:B6)
D21:D35,K21:K31	D21	=RPTBYNR(A3:B6)
F21:F35,M21:M31	F21	=RPTNTIMES(A3:B6)
Dynamic array formulas.

 

[Xlambda]

Saw Karina's video. She used the VSTACK's, not you.
What triggered me to solve a similar challenge was a comment posted by "EXECUTIVE ENIGNEER" under latest MrExcel YT: Excel IF Contains - 2514 , (where you had also a post.)
We got triggered by different sources.

 

[Xlambda]

Off topic, a question I wanted to ask you since Excel launched IMAGE function, does it make any sense, or will it be ever possible to integrate IMAGE results on mini sheets using Xl2bb add-in?
What's your opinion on that?

 

[smozgur]

I am positive you did not read my latest posts under AFLAT thread solving same challenge using same XLOOKUP approach. You would have noticed that the VSTACKs are not needed.

No, I didn't but now I did, it is beautiful!

And RPTBYNR above is perfect! You know, I am not a perfect function person but a friend asked me for something similar, and I was also curious about how to generate more rows/cols than the source has other than using recursion, and learning the STACK functions made my day. Now I learned another way of that, thank you!
(I only wish that F9 doesn't return #NAME! for the named calculations, it really makes it harder to evaluate the formulas - I have to copy and paste references to analyze the functions.)

Off topic, a question I wanted to ask you since Excel launched IMAGE function, does it make any sense, or will it be ever possible to integrate IMAGE results on mini sheets using Xl2bb add-in?
What's your opinion on that?

I can see at least two potential problems right away with having XL2BB render the URL given to the IMAGE function, and one of them is could be a real pain (dealing with the idea of allowing inner tags in the formula tag). Although I can't promise the implementation, I'll look if I can find a workaround.

 

[Xlambda]

You're welcome!
Regarding IMAGE integration, if the cost of integration exceeds the benefits, maybe you don't have to bother spending time on it.
Real benefits, apart from that it will look cool (and nobody has it) are difficult to spot. Anyhow, for images we already have all the fine tools we need, as upload able attachments.

 

[MrExcel]

I saw the same question from "EXECUTIVE ENIGNEER" on YouTube. It looks like the OP deleted the question, which caused all of the answers to be deleted. My approach to this was a VBA UDF:

Function Explode(r)
    Dim Answer() As Variant
    CallerRows = r.Rows.Count
    FinalSize = 0
    FinalSize = Application.WorksheetFunction.Sum(r.Columns(2))
    ReDim Answer(1 To FinalSize)
    Ctr = 1
    Debug.Print CallerRows
    For i = 1 To CallerRows
        For j = 1 To r(i, 2).Value
            Answer(Ctr) = r(i, 1).Value
            Ctr = Ctr + 1
        Next j
    Next i
    Explode = Application.Transpose(Answer)
   
End Function

 

[smozgur]

I saw the same question from "EXECUTIVE ENIGNEER" on YouTube. It looks like the OP deleted the question, which caused all of the answers to be deleted. My approach to this was a VBA UDF:

Function Explode(r)
    Dim Answer() As Variant
    CallerRows = r.Rows.Count
    FinalSize = 0
    FinalSize = Application.WorksheetFunction.Sum(r.Columns(2))
    ReDim Answer(1 To FinalSize)
    Ctr = 1
    Debug.Print CallerRows
    For i = 1 To CallerRows
        For j = 1 To r(i, 2).Value
            Answer(Ctr) = r(i, 1).Value
            Ctr = Ctr + 1
        Next j
    Next i
    Explode = Application.Transpose(Answer)
 
End Function

Thanks! VBA will be always my favorite!

 

[smozgur]

Power Query version to complete the solution series.

let
    // Load the source table data range
    Source = Excel.CurrentWorkbook(){[Name="ProductTable"]}[Content],

    // Set the column types
    SetTypes = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Repeat", Int64.Type}}),

    // Convert the Repeat column values to many list items by the repeat value
    Repeated = Table.TransformColumns(SetTypes, {"Repeat", each List.Numbers(1,_), type list}),

    // Expand the list items in the converted Repeat column to get the necessary number of repeated rows
    // Note that Product column values are repated for each list item (thát's the whole idea)
    Expanded = Table.ExpandListColumn(Repeated, "Repeat"),

    // Remove items with 0 number of Repeat since those will generate null values (this could be also done at the beginning)
    NullsRemoved = Table.SelectRows(Expanded, each [Repeat] <> null and [Repeat] <> ""),

    // We don't need the Repeat column anymore
    Result = Table.RemoveColumns(NullsRemoved,{"Repeat"})
in
    Result

 

[st001]

I think this may be more robust.
It works on arrays, returning rows (not just the first column) 'n' times, based on the value in the last column
There is a shortcoming in the current XLOOKUP function in that it cannot return rows of results if the lookup array is a vector (rather than a single value), so I've had to use the REDUCE / VSTACK combo. (I tried MAP (vector, LAMBDA(x, XLOOKUP...), but that didn't work either)

AROWREPEAT: (the final calculation function - DROP - is the fix for the current XLOOKUP function)

=LAMBDA(ar,
    LET(
        b, TAKE(ar, , -1),
        c, SEQUENCE(SUM(b)),
        d, SCAN(0, b, LAMBDA(v, i, v + i)),
        DROP(REDUCE(0, c, LAMBDA(a, e, VSTACK(a, XLOOKUP(e, d, DROP(ar,,-1), , 1, 1)))), 1)
    )
)