REPEATBYNUMBER

=REPEATBYNUMBER(range)
range
Required. The range (or table) has the first column as the item name and the last column as the repeat number.
REPEATBYNUMBER 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.
smozgur

smozgur

BatCoder
Builders Club Member
Joined
Feb 28, 2002
Messages
2,571
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.

Excel Formula: 
=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
ABCDEF
1ProductRepeatResult (range param)Result (table param)
2Apple5AppleApple
3Banana4AppleApple
4Cherry2AppleApple
5Strawberry4AppleApple
6AppleApple
7BananaBanana
8BananaBanana
9BananaBanana
10BananaBanana
11CherryCherry
12CherryCherry
13StrawberryStrawberry
14StrawberryStrawberry
15StrawberryStrawberry
16StrawberryStrawberry
Sheet4
Cell Formulas
RangeFormula
D2:D16D2=REPEATBYNUMBER(A2:B5)
F2:F16F2=REPEATBYNUMBER(ProductTable)
Dynamic array formulas.
 
Last edited:
Upvote 1
smozgur said:
Power Query version to complete the solution series.

Power Query: 
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
Click to expand...

another PQ approach:

Power Query: 
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
 
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)
Excel Formula: 
=LAMBDA(a,
    LET(
        u, UNIQUE(a),
        HSTACK(u, BYROW(u, LAMBDA(x, SUM(--BYROW(x = a, LAMBDA(y, AND(y)))))))
    )
)
repeat product names.xlsx
ABCDEFGHIJKL
1
2a=IMPLODE(B3:B17)
3AppleApple5
4AppleBanana4
5AppleCherry2
6AppleStraberry4
7Apple
8Banana
9Bananacheck
10Banana=COUNTIF(B3:B17,UNIQUE(B3:B17))
11Banana5
12Cherry4
13Cherry2
14Straberry4
15Straberry
16Straberry
17Straberry
18
19a=IMPLODE(C20:E33)
20aeiaei3
21aeibfj5
22aeicgk4
23bfjdhl2
24bfj
25bfj
26bfj
27bfj
28cgk
29cgk
30cgk
31cgk
32dhl
33dhl
34
Sheet6
Cell Formulas
RangeFormula
F2,G19,G10F2=FORMULATEXT(F3)
F3:G6F3=IMPLODE(B3:B17)
G11:G14G11=COUNTIF(B3:B17,UNIQUE(B3:B17))
G20:J23G20=IMPLODE(C20:E33)
Dynamic array formulas.
 
smozgur said:
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.

Excel Formula: 
=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,"))
Click to expand...

st001 said:
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)
Excel Formula: 
=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)
    )
)
Click to expand...
I used the following solution:
=TOCOL(TEXTSPLIT(TEXTJOIN("",1,REPT(A4:A11&"|",B4:B11)),"|",,1,,""))
 
I used the following solution:
=TOCOL(TEXTSPLIT(TEXTJOIN("",1,REPT(A4:A11&"|",B4:B11)),"|",,1,,""))
 
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) )
 
Xlambda said:
RWSRPT(ar) Rows Repeat function, no need of SCAN, REDUCE, VSTACK, or XLOOKUP
Excel Formula: 
=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
ABCDEFGHIJ
1
2ar=RWSRPT(B3:E6)
3aei3aei
4bfj5aei
5cgk4aei
6dhl2bfj
7bfj
8bfj
9bfj
10bfj
11cgk
12cgk
13cgk
14cgk
15dhl
16dhl
17
18
19
20ar=RWSRPT(B21:E24)
21aei3aei
22bfj5aei
23cgk0aei
24dhl2bfj
25bfj
26bfj
27bfj
28bfj
29dhl
30dhl
31
RWSRPT
Cell Formulas
RangeFormula
G2,G20G2=FORMULATEXT(G3)
G3:I16,G21:I30G3=RWSRPT(B3:E6)
Dynamic array formulas.
Click to expand...

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(n)), 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 said:
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(n)), 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!
Click to expand...
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),
 
McKay_S said:
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(n)), 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!
Click to expand...
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!! 🙏
 
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?
 
st001 said:
Any ideas?
Click to expand...
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. ✌
 
You must log in or register to reply here.

Forum statistics

Threads
1,216,503
Messages
6,131,022
Members
449,616
Latest member
PsychoCube

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top