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

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
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
ABCDEFGHIJKLM
1ProductRepeat
2Apple5=XLOOKUP(SEQUENCE(SUM(B2:B5)),SCAN(1,B1:B5,LAMBDA(a,b,SUM(a,b))),A2:A6,,-1)
3Banana0Apple
4Cherry2Apple
5Strawberry1Apple
6Apple
7Apple
8Banana
9Banana
10Strawberry
11
Sheet1
Cell Formulas
RangeFormula
E2E2=FORMULATEXT(E3)
E3:E10E3=XLOOKUP(SEQUENCE(SUM(B2:B5)),SCAN(1,B1:B5,LAMBDA(a,b,SUM(a,b))),A2:A6,,-1)
Dynamic array formulas.
 
Try this version with blank or 0

Complex Formulas.xlsm
ABCF
1ProductRepeat
2Apple5Apple
3Banana3Apple
4Cherry2Apple
5Strawberry1Apple
6Apple
7Banana
8Banana
9Banana
10Cherry
11Cherry
12Strawberry
13
1b
Cell Formulas
RangeFormula
F2:F12F2=LOOKUP(SEQUENCE(SUM(rB)),SCAN(1,rB,LAMBDA(a,b,a+N(b))),A2:A5)
Dynamic array formulas.
Named Ranges
NameRefers ToCells
rB='1b'!$B$1:$B$5F2
 
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
ABCDEFGHIJK
1ProductRepeat
2Apple5=LOOKUP(SEQUENCE(SUM(B1:B5)),SCAN(1,B1:B5,LAMBDA(a,b,a+N(b))),A2:A5)
3Banana-3Apple
4Cherry2Apple
5Strawberry1Cherry
6Cherry
7Strawberry
8
93 apples missing
10
Sheet1
Cell Formulas
RangeFormula
D2D2=FORMULATEXT(D3)
D3:D7D3=LOOKUP(SEQUENCE(SUM(B1:B5)),SCAN(1,B1:B5,LAMBDA(a,b,a+N(b))),A2:A5)
Dynamic array formulas.
 
Might not be pretty, but it works.

Book1
ABCD
1ProductRepeatFX
2Apple5Apple
3Banana-3Apple
4Cherry2Apple
5Strawberry1Apple
6Apple
7Cherry
8Cherry
9Strawberry
Sheet3
Cell Formulas
RangeFormula
D2:D9D2=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.
 
Last edited:
Very good! I created a Lambda function and posted it below.

Complex Formulas.xlsm
ABCD
1ProductRepeat
2Apple5Apple
3Banana3Apple
4Cherry2Apple
5Strawberry1Apple
6Apple
7Banana
8Banana
9Banana
10Cherry
11Cherry
12Strawberry
13
1b
Cell Formulas
RangeFormula
D2:D12D2=RepeatByNumber(A2:A5,B2:B5)
Dynamic array formulas.
Lambda Functions
NameFormula
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)))
 
Just for fun. Here is a way to do it using Power Query.

EXCEL
ABCD
1ProductRepeatRepeatByNumber
2Apple5Apple
3Banana-3Apple
4Cherry2Apple
5Strawberry1Apple
6Apple
7Cherry
8Cherry
9Strawberry
Sheet4


Power Query:
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
 
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
ABCDEFGHIJKLMN
1ProductRepeat
2Apple5=LAMBDA(a,r,INDEX(a,TOCOL(IF(r>=SEQUENCE(,MAX(r)),SEQUENCE(ROWS(r)),NA()),2)))(A2:A8,B2:B8)
3Banana-3Apple
4Cherry2AppleOr a simple formula
5Strawberry0Apple=LET(r,B2:B8,INDEX(A2:A8,TOCOL(IF(r>=SEQUENCE(,MAX(r)),SEQUENCE(ROWS(r)),NA()),2)))
6Melon4AppleApple
7Grapes3AppleApple
8Lemon2CherryApple
9CherryApple
10MelonApple
11MelonCherry
12MelonCherry
13MelonMelon
14GrapesMelon
15GrapesMelon
16GrapesMelon
17LemonGrapes
18LemonGrapes
19Grapes
20Lemon
21Lemon
22
Sheet1
Cell Formulas
RangeFormula
D2,F5D2=FORMULATEXT(D3)
D3:D18D3=LAMBDA(a,r,INDEX(a,TOCOL(IF(r>=SEQUENCE(,MAX(r)),SEQUENCE(ROWS(r)),NA()),2)))(A2:A8,B2:B8)
F6:F21F6=LET(r,B2:B8,INDEX(A2:A8,TOCOL(IF(r>=SEQUENCE(,MAX(r)),SEQUENCE(ROWS(r)),NA()),2)))
Dynamic array formulas.
 
For a little bit more fun... This is how I did it using Python.

EXCEL
ABCD
1ProductRepeatPython
2Apple5Apple
3Banana-3Apple
4Cherry2Apple
5Strawberry1Apple
6Apple
7Cherry
8Cherry
9Strawberry
Sheet1
Cell Formulas
RangeFormula
D2:D9D2=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.


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

To run Python, what version of Excel is required.

I am just curious.
 
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.
 

Forum statistics

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

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