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.

 

[JGordon11]

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

another PQ approach:

let 
    Source = Excel.CurrentWorkbook(){[Name="ProductTable"]}[Content],
    lst = List.Combine(Table.TransformRows(Source, each List.Repeat({[Product]}, [Repeat]))),
    Result = Table.FromList(lst,null,{"Product"})
in 
    Result

 

[Xlambda]

Liked Bill's UDF function's name "Explode" so much that I couldn't resist not writing IMPLODE
Can implode column vectors or array's rows. Nested BYROWs
IMPLODE(a)

=LAMBDA(a,
    LET(
        u, UNIQUE(a),
        HSTACK(u, BYROW(u, LAMBDA(x, SUM(--BYROW(x = a, LAMBDA(y, AND(y)))))))
    )
)

repeat product names.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1
2		a				=IMPLODE(B3:B17)
3		Apple				Apple	5
4		Apple				Banana	4
5		Apple				Cherry	2
6		Apple				Straberry	4
7		Apple
8		Banana
9		Banana					check
10		Banana					=COUNTIF(B3:B17,UNIQUE(B3:B17))
11		Banana					5
12		Cherry					4
13		Cherry					2
14		Straberry					4
15		Straberry
16		Straberry
17		Straberry
18
19			a				=IMPLODE(C20:E33)
20			a	e	i		a	e	i	3
21			a	e	i		b	f	j	5
22			a	e	i		c	g	k	4
23			b	f	j		d	h	l	2
24			b	f	j
25			b	f	j
26			b	f	j
27			b	f	j
28			c	g	k
29			c	g	k
30			c	g	k
31			c	g	k
32			d	h	l
33			d	h	l
34
Sheet6

Cell Formulas
Range	Formula
F2,G19,G10	F2	=FORMULATEXT(F3)
F3:G6	F3	=IMPLODE(B3:B17)
G11:G14	G11	=COUNTIF(B3:B17,UNIQUE(B3:B17))
G20:J23	G20	=IMPLODE(C20:E33)
Dynamic array formulas.

 

[JoseMorato]

A função REPEATBYNUMBER tem uma faixa (ou tabela) que tem a primeira coluna como nome do item e a última coluna, pois o número de repetição retorna uma nova coluna de linhas com os nomes dos itens repetidos pelo número de vezes exigido. (Aprendi a ideia principal comKarina Adcock, e usei a função TAKE para fazê-la funcionar com um único parâmetro de gama/tabela).

EDIT: Esta função pressupõe que cada produto nos dados de origem existe no inventário (Repetir >= 1). Para obter uma implementação alternativa que considere repetir = 0, consulte afunção RPTBYNR @Xlambdaabaixo.

=LAMBDA(dados,
XLOOKUP
SEQUÊNCIA (SOMA(DADOS,,-1)),
VSTACK(1, SCAN(1,TAKE (dados,,-1), LAMBDA(a,b,a+b)),
VSTACK (TAKE(dados,,1)"),
        ,
        -1
    )
)[/CÓDIGO]
[RANGE=rs:16|cs:6|w:RepeatByNumber|cls:xl2bb-200|s:Sheet4|tw:368] [XR] [XH] [/XH] [XH=w:51] A[/XH][XH=w:51]B[/XH][XH=w:51]C[/XH][XH=w:96]D[/XH][XH=w:22]E[/XH][XH=w:96]F[/XH][/XR][XR][XR][XH]1[XH]1[/XH]1[/XH]1[/XH]1[/XH]1[/XH]1[/XH]1[/XH]1[/XH]1[/XH]1[/XH]1[/XH]1[/XH]1[/XH]1[/XH]1[/XH]1[/XH]1[/XH]1[/XH]1[/XH]1[/XH]1[/XH]1[/XH]1[/XH]1[/XH]1[/XH]1[/XH]1[/XH]1[/XH]1[/XH XH][XD=h:l|fw:b|bc:4472C4|c:FFFFFF|ch:14.25|cls:bl bt bb]Produto[/XD][XD=h:l|fw:b|bc:4472C4|c: FFFFFF|cls:bt br bb]Repita[/XD][XD=cls:bl][/XD][XD=h:l|BC:E2EFDA]Resultado (intervalo param)[/XD][XD][XD][XD=h:l|bc:E2EF]Resultado (param mesa)[/XD][/XD][/XD][XD=h:l|bc:E2EF]Resultado (param mesa)[/XD][/XD][XD=h:l|bc:E2EF]Resultado (param)[/XD][/XD][XD=h:l|bc:E2EF]Resultado (param mesa)[/XD][/XD][XD=h:l|BC:E2EF]Resultado (param mesa)[/XD][/XD][XD=h:l|bc:E2EFDA]Resultado (param mesa)[/XD][/XD][XD=h:l|bc:E2EF]Resultado (param)[/XD][/XD][XD=h:lR][XR][XH]2[/XH][XD=h:l|bc:D9E1F2|ch:14.25|cls:bl bt bb]Apple[/XD][XD=bc:D9E1F2|cls:bt br bb]5[/XD][XD=cls: bl][/XD][XD=h:l|cls:fx spill][FORMULA===REPEATBYNUMBER(A2:B5)]]Apple[/FORMULA][/XD][XD][/XD][XD=h:l|cls:fx spill][FORMULA===REPEATBYNUMBER(ProductTable)]]Apple[/FORMULA][/XD][/XR][XR][XH]3[/XH][XD=h:l|ch:14.25|cls:bl bt bb]Banana[/XD][XD=cls:bt br bb]4[/XD][XD=cls:bl][/XD][XD=h: l|cls:spill|tx:]Apple[/XD][XD][/XD][XD=h:l|cls:spill|tx:]Apple[]Apple[]Apple[/XD][/XR][XR][XH]4[/XH][XD=h:l|bc:D9E1F2|ch:14.25|cls:bl bt bb]Cherry[/XD][XD=bc:D9E1F2|cls:bt br bb]2[/XD][XD=cls:bl][/XD][XD=h:l|cls:spill|tx:]Apple[/XD][XD][/XD][XD l|cls:derramamento|tx:]Apple[/XD][/XR][XR][XH]5[/XH][XD=h:l|ch:14.25|cls:bl bt bb]Morango[/XD][XD=cls:bt br bb]4[/XD][XD=cls:bl][[/XD][XD=h:l|cls:derramamento|tx:]Apple[/XD][XD][/XD][XD=h:l|cls:derramamento|tx:]Apple[/XD][/XR][XR][XH]6[/XH][XD=ch:14.25|cls: bt][/XD][XD=cls:bt][/XD][XD][/XD][XD=h:l|cls:derramamento|tx:]Apple[/XD][XD][/XD][XD=h:l|cls:derramamento|tx:]Apple[/XD][/XR][XR][XH]7[/XH][XD=ch:14.25][/XD][XD][/XD][/X[XD][XD][XD][XD=h:l|cls:derramamento|tx:]Banana[/XD][XD][/XD][XD=h:l|cls:derramamento|tx:]Banana[/XD][/XR][XR][XH]8[/XH][XD=ch:14.25][/XD][XD][/XD][XD][XD][/XD][XD][XD=h: l|cls:derramamento|tx:]Banana[/XD][XD][/XD][XD=h:l|cls:derramamento|tx:]Banana[/XD][/XR][XR][XH]9[/XH][XD=ch:14.25][/XD][XD][/XD][XD][/XD][XD][XD=h:l, |cls:derramamento|tx:]Banana[/XD][/XD][XD][XD=h:l|cls:derramamento|tx:]Banana[/XD][/XR][XR][XH]10[/XH][XD=ch:14.25][/XD][XD][/XD][XD][/XD][XD][XD=h: l|cls:derramamento|tx:]Banana[/XD][XD][/XD][XD=h:l|cls:derramamento|tx:]Banana[/XD][/XR][XR][XH]11[/XH][XD=ch:14.25][/XD][XD][/XD][XD][/XD][XD][XD=h:l|,cls:derramamento|tx:]Cereja[/XD][XD][/XD][XD=h:l|cls:derramamento|tx:]Cereja[/XD][/XD][/X [XR][XH]12[/XH][XD=ch:14.25][/XD][XD][/XD][XD][/XD][XD][XD=h: l|cls:derramamento|tx:]Cereja[/XD][XD][/XD][XD=h:l|cls:derramamento|tx:]Cereja[/XD][/XR][XR][XH]13[/XH][XD=ch:14.25][/XD][XD][/XD][XD][/XD][XD][XD=h:l|, XD=h|:l|cls:derramamento|tx:]Morango[/XD][/XD][XD=h:l|cls:derramamento|tx:]Morango[/XD][/XD][/X [XR][XH]14[/XH][XD=ch:14.25][/XD][XD][/XD][XD][/XD][XD][XD=h: l|cls:derramamento|tx:]Morango[/XD][XD][/XD][XD=h:l|cls:derramamento|tx:]Morango[/XD][/XD][/XR][XR][XH]15[/XH][XD=ch:14.25][/XD][XD][/XD][XD][/XD][XD][XD=h:l|cls:spill|tx:]Morango[/XD][XD][/XD][XD=h:l|cls:derramamento|tx:]Morango[/XD][/XR][XR][XH]16[/XH][XD=ch:14.25][/XD][XD][/XD][XD][/XD][XD][XD=h: l|cls:derramamento|tx:]Morango[/XD][XD][/XD][XD=h:l|cls:derramamento|tx:]Morango[/X[/XR][/RANGE][RANGE=cls:xl2bb-extra-200|t:cf|f:xtrs][XR][XD]D2:D16[/X[XD=fw:b]D2[/XD][XD=c:548235]=REPEATBYNUMBER(A2:B5)[/XD][/XR][XR][XD][XD]F2:F16[/XD][XD=fw:b]F2[/XD][XD=c:548235]=REPEATBYNUMBER(ProductTable)[/XD][/XR][/RANGE]
[/QUOTE]

=TOCOL(TEXTSPLIT(TEXTJOIN("",",1,REPT(A4:A11&"|",B4:B11)),"|",,1,"))

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)
    )
)

I used the following solution:
=TOCOL(TEXTSPLIT(TEXTJOIN("",1,REPT(A4:A11&"|",B4:B11)),"|",,1,,""))

 

[JoseMorato]

I used the following solution:
=TOCOL(TEXTSPLIT(TEXTJOIN("",1,REPT(A4:A11&"|",B4:B11)),"|",,1,,""))

 

[st001]

This is also an excellent solution!
(Though if the array is 3 columns wide, with the repeat 'n' in the last' it will return all results into a single column, rather than repeat for each row (the first two columns) )

 

[McKay_S]

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.

Really like the elegance and speed (thanks for showing us that side-by-side test!) of this variation.

For this, and any of the versions people are posting here, I would suggest adding an error check for non-numbers in the number column. I've been testing it out and non-numbers really break all these lambdas. Where this could easily come in is if someone includes the header row in the range.

The simplest I've figured so far is to add a row the lambda:
n_chk, IF( NOT(ISNUMBER), 0, n),

And then update the TOCOL line to change the two references to variable "n" to "n_chk"
My version here replaces any non-number with 0, but of course that can be changed to whatever behaviour people thing makes the most sense.

I've followed the forums for a while, but just joined to start participating and adding in my experiments and creations. So thanks for having me!

 

[McKay_S]

Really like the elegance and speed (thanks for showing us that side-by-side test!) of this variation.

For this, and any of the versions people are posting here, I would suggest adding an error check for non-numbers in the number column. I've been testing it out and non-numbers really break all these lambdas. Where this could easily come in is if someone includes the header row in the range.

The simplest I've figured so far is to add a row the lambda:
n_chk, IF( NOT(ISNUMBER), 0, n),

And then update the TOCOL line to change the two references to variable "n" to "n_chk"
My version here replaces any non-number with 0, but of course that can be changed to whatever behaviour people thing makes the most sense.

I've followed the forums for a while, but just joined to start participating and adding in my experiments and creations. So thanks for having me!

Whoops, as a new member, I can't edit my post and didn't realise the code would generate a emoji! Sorry about that!

The variable is suppose to be n_chk, IF( NOT(ISNUMBER( n )), 0, n),

 

[Xlambda]

Really like the elegance and speed (thanks for showing us that side-by-side test!) of this variation.

For this, and any of the versions people are posting here, I would suggest adding an error check for non-numbers in the number column. I've been testing it out and non-numbers really break all these lambdas. Where this could easily come in is if someone includes the header row in the range.

The simplest I've figured so far is to add a row the lambda:
n_chk, IF( NOT(ISNUMBER), 0, n),

And then update the TOCOL line to change the two references to variable "n" to "n_chk"
My version here replaces any non-number with 0, but of course that can be changed to whatever behaviour people thing makes the most sense.

I've followed the forums for a while, but just joined to start participating and adding in my experiments and creations. So thanks for having me!

Thank you McKay_S, glad you liked it, and welcome to the forum!!
Error management is very important in general indeed. Sometimes, to cover all possible error management scenarios, formula's/function's size can double. Context is important also, and anybody can adapt a function, as long as the concept is robust with the data types was designed for, to different gradients of safety mesures.
Luckily, lambda arguments can accept array calculations, and this is great help to constrain source input data into proper data that a function was designed to handle.
The reason I was not bothered by ISNUMBER check is that this task is unlikely to be a stand alone task, looks more as a "subrutine" task of a bigger picture task, like unpivoting.
And an unpivoting routine, based on numeric patterns, will always deliver numbers, and those numbers will "feed" our "subrutine".
That's why I considered the possibility of having 0's, something that an indexing or counting calculation can really deliver as a numeric pattern.
To cut the long story short, whenever anybody will need this as a stand alone task, ISNUMBER check is a must. . Thanks for sharing!!

 

[st001]

Hi,

Is there a solution to this

=BYROW([vertical column], LAMBDA(row, XLOOKUP(row, lookuparray [same height as vertical column] , return array [same height as vertical column - but with a large width] )))

I keep getting a #CALC! error

I tried the RBYROW function...it works!

But then returns a #CALC error if the height of the vertical column is more than 25-27...

(the width of the return array is about 300 columns...)

Any ideas?

 

[Xlambda]

Any ideas?

This RBYROW, mine?
Should work fine, needs only error management, if a single row function delivers an error, compromises the entire stack of row by rows. For filter or xlookup use if not found argument to deliver empty string or whatever you want. For other scenarios use IFERROR(...in the lambda helper function construction argument like we do for any built-in lambda helper functions.
Let me know how it turns out. ✌