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
Thanks for the information.
I am signed in for Beta 365 Insider but the updates have not arrived for months or years.
I just try to help people with basic Excel. Good luck with Python and other sophisticated tools. Dave
 
Here are 2 Xmas trees for you guys, one to always remember that numbers can be +, -, 0 and one that uses offset letters as ornament, to remember the questions never answered.😊🎄🎅✌️
function here: AXMAS
Book1.xlsx
ABCDEFG
1
2⭐⭐⭐⭐
3‖‖‖‖
40-os
5-0-0ofso
6--0--+sfftos
7-00++-+-tseootfs
80+00----++ffesssssss
90+----00--+0tsfffoofesot
10+-0+-0++0+-0+-efsesotseoessf
11-0--00-+0-+0+-0+oseottfssfoefeto
12-00-+-+++++-000---ttsfofoffotteefsso
13-++++-0-++------0-00tstetototefoeeossfto
14-000+00-0++-0-0-+0-+++tffeeeefsfeestostttees
15-0----0-+0+00-00-0-00+-0eotoefefetefesooseofefeo
16++-0++0+--+-++-+-0-+0+-+0+ttstftetefftfeotsfsfteoste
170--+0--0+0++-++++0-++0++0++0otfoesoefeosttofsteseffsttot
1800-00-+0+++0+--+-0+0-0+0+00-0+tefsttesoftfosssfftetsofffsfst
19-+0++0+--++--0+---00-+-00+0-+0++teotososftsotoseotfeostootsstoss
200+++000++-0++0+0+0++-00+0++0+00+-0tessossofototfssostsftssfossfostos
21+--0000+--++--0++-++0-0+-0-0++0+++-0offfseftetosotftfetosefsotttseoesooo
220++-+--0+-0-0++0+-000-+-+0-++-+00+0-0+sfoeeeststttssteeofftofofftfottfstseot
23-0-0-++000-+000-0000+0+++0-00-++-0++0-+0ofeefofeeefeftottotooststsetsttetstssosf
240+---0---++++-+0++0-0-0+0-0+-00+00+-++++-0tooefsfoooooeooffefetteeseseoessteefffteeo
250---++-+++---00+++-0-0--0++--0-0-+0-00---+++osfosstfstfttftefeeseotffetofeteseestfseofst
26000+-+-+-0-++000---+++0000+-+0-+00+0++-+----0-sttoeessfeotstssftsfeesseesefstosofstfstseefee
27🎁▓▓🎁🎁▓▓🎁
28
Sheet1
Cell Formulas
RangeFormula
B2:C27B2=AXMAS(23,{"-";"+";"0"})
F2:G27F2=AXMAS(23,{"o";"f";"s";"e";"t"})
Dynamic array formulas.
 
Not sure if this thread is strictly for lambda functions only but a solution with no lambda.
Book1
ABCD
1ProductRepeat
2Apple5Apple
3Banana3Apple
4Cherry2Apple
5Strawberry1Apple
6Apple
7Banana
8Banana
9Banana
10Cherry
11Cherry
12Strawberry
Sheet3
Cell Formulas
RangeFormula
D2:D12D2=TOCOL(IFS(B2:B5>=SEQUENCE(,MAX(B2:B5)),A2:A5),2)
Dynamic array formulas.
 
Cubist said:
You're absolutely right, this is a great solution, and I believe it's one that's been explored in this forum already. You will still need a lambda to turn it into a custom function in your name manager.
Click to expand...
 
Cubist said:
Not sure if this thread is strictly for lambda functions only but a solution with no lambda.
Click to expand...
Yes, this thread and the forum - "Excel LAMBDA Functions" is strictly for lambda functions.
 
Cubist said:
You're absolutely right, this is a great solution, and I believe it's one that's been explored in this forum already. You will still need a lambda to turn it into a custom function in your name manager.
Click to expand...
I missed that. Thanks.
 
You must log in or register to reply here.

Forum statistics

Threads
1,216,499
Messages
6,131,012
Members
449,613
Latest member
MedDash99

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