REPEATBYNUMBER

smozgur · Sep 17, 2022

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

A

B

C

D

E

F

1

Product

Repeat

Result (range param)

Result (table param)

2

Apple

5

Apple

3

Banana

4

Apple

4

Cherry

2

Apple

5

Strawberry

4

Apple

6

Apple

7

Banana

8

Banana

9

Banana

10

Banana

11

Cherry

12

Cherry

13

Strawberry

14

Strawberry

15

Strawberry

16

Strawberry

Sheet4

Cell Formulas
Range	Formula
D2:D16	D2	=REPEATBYNUMBER(A2:B5)
F2:F16	F2	=REPEATBYNUMBER(ProductTable)
Dynamic array formulas.

Xlambda · Dec 27, 2023

Dave Patton said:
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

A

B

C

D

E

F

G

H

I

J

K

L

M

1

Product

Repeat

2

Apple

5

=XLOOKUP(SEQUENCE(SUM(B2:B5)),SCAN(1,B1:B5,LAMBDA(a,b,SUM(a,b))),A2:A6,,-1)

3

Banana

0

Apple

4

Cherry

2

Apple

5

Strawberry

1

Apple

6

Apple

7

Apple

8

Banana

9

Banana

10

Strawberry

11

Sheet1

Cell Formulas
Range	Formula
E2	E2	=FORMULATEXT(E3)
E3:E10	E3	=XLOOKUP(SEQUENCE(SUM(B2:B5)),SCAN(1,B1:B5,LAMBDA(a,b,SUM(a,b))),A2:A6,,-1)
Dynamic array formulas.

Dave Patton · Dec 27, 2023

Try this version with blank or 0

Complex Formulas.xlsm

A

B

C

F

1

Product

Repeat

2

Apple

5

Apple

3

Banana

3

Apple

4

Cherry

2

Apple

5

Strawberry

1

Apple

6

Apple

7

Banana

8

Banana

9

Banana

10

Cherry

11

Cherry

12

Strawberry

13

1b

Cell Formulas
Range	Formula
F2:F12	F2	=LOOKUP(SEQUENCE(SUM(rB)),SCAN(1,rB,LAMBDA(a,b,a+N(b))),A2:A5)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
rB	='1b'!$B$1:$B$5	F2

Xlambda · Dec 27, 2023

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

A

B

C

D

E

F

G

H

I

J

K

1

Product

Repeat

2

Apple

5

=LOOKUP(SEQUENCE(SUM(B1:B5)),SCAN(1,B1:B5,LAMBDA(a,b,a+N(b))),A2:A5)

3

Banana

-3

Apple

4

Cherry

2

Apple

5

Strawberry

1

Cherry

6

Cherry

7

Strawberry

8

9

3 apples missing

10

Sheet1

Cell Formulas
Range	Formula
D2	D2	=FORMULATEXT(D3)
D3:D7	D3	=LOOKUP(SEQUENCE(SUM(B1:B5)),SCAN(1,B1:B5,LAMBDA(a,b,a+N(b))),A2:A5)
Dynamic array formulas.

lrobbo314 · Dec 27, 2023

Might not be pretty, but it works.

Book1

A

B

C

D

1

Product

Repeat

FX

2

Apple

5

Apple

3

Banana

-3

Apple

4

Cherry

2

Apple

5

Strawberry

1

Apple

6

Apple

7

Cherry

8

Cherry

9

Strawberry

Sheet3

Cell Formulas
Range	Formula
D2:D9	D2	=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.

Dave Patton · Dec 27, 2023

Very good! I created a Lambda function and posted it below.

Complex Formulas.xlsm

A

B

C

D

1

Product

Repeat

2

Apple

5

Apple

3

Banana

3

Apple

4

Cherry

2

Apple

5

Strawberry

1

Apple

6

Apple

7

Banana

8

Banana

9

Banana

10

Cherry

11

Cherry

12

Strawberry

13

1b

Cell Formulas
Range	Formula
D2:D12	D2	=RepeatByNumber(A2:A5,B2:B5)
Dynamic array formulas.

Lambda Functions
Name	Formula
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)))

lrobbo314 · Dec 27, 2023

Just for fun. Here is a way to do it using Power Query.

EXCEL

A

B

C

D

1

Product

Repeat

RepeatByNumber

2

Apple

5

Apple

3

Banana

-3

Apple

4

Cherry

2

Apple

5

Strawberry

1

Apple

6

Apple

7

Cherry

8

Cherry

9

Strawberry

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

Xlambda · Dec 27, 2023

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
A B C D E F G H I J K L M N
1 Product Repeat
2 Apple 5 =LAMBDA(a,r,INDEX(a,TOCOL(IF(r>=SEQUENCE(,MAX(r)),SEQUENCE(ROWS(r)),NA()),2)))(A2:A8,B2:B8)
3 Banana -3 Apple
4 Cherry 2 Apple Or a simple formula
5 Strawberry 0 Apple =LET(r,B2:B8,INDEX(A2:A8,TOCOL(IF(r>=SEQUENCE(,MAX(r)),SEQUENCE(ROWS(r)),NA()),2)))
6 Melon 4 Apple Apple
7 Grapes 3 Apple Apple
8 Lemon 2 Cherry Apple
9 Cherry Apple
10 Melon Apple
11 Melon Cherry
12 Melon Cherry
13 Melon Melon
14 Grapes Melon
15 Grapes Melon
16 Grapes Melon
17 Lemon Grapes
18 Lemon Grapes
19 Grapes
20 Lemon
21 Lemon
22
Sheet1
Cell Formulas
Range Formula
D2,F5 D2 =FORMULATEXT(D3)
D3:D18 D3 =LAMBDA(a,r,INDEX(a,TOCOL(IF(r>=SEQUENCE(,MAX(r)),SEQUENCE(ROWS(r)),NA()),2)))(A2:A8,B2:B8)
F6:F21 F6 =LET(r,B2:B8,INDEX(A2:A8,TOCOL(IF(r>=SEQUENCE(,MAX(r)),SEQUENCE(ROWS(r)),NA()),2)))
Dynamic array formulas.

lrobbo314 · Dec 27, 2023

For a little bit more fun... This is how I did it using Python.

EXCEL

A

B

C

D

1

Product

Repeat

Python

2

Apple

5

Apple

3

Banana

-3

Apple

4

Cherry

2

Apple

5

Strawberry

1

Apple

6

Apple

7

Cherry

8

Cherry

9

Strawberry

Sheet1

Cell Formulas
Range	Formula
D2:D9	D2	=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]

Dave Patton · Dec 27, 2023

Hello lrobbo314

To run Python, what version of Excel is required.

I am just curious.

lrobbo314 · Dec 27, 2023

Dave Patton said:
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.

REPEATBYNUMBER

smozgur

BatCoder

Xlambda

Well-known Member

Dave Patton

Well-known Member

Xlambda

Well-known Member

lrobbo314

Well-known Member

Dave Patton

Well-known Member

lrobbo314

Well-known Member

Xlambda

Well-known Member

lrobbo314

Well-known Member

Dave Patton

Well-known Member

lrobbo314

Well-known Member

Forum statistics

Share this page

Cell Formulas
Range	Formula
D2,F5	D2	=FORMULATEXT(D3)
D3:D18	D3	=LAMBDA(a,r,INDEX(a,TOCOL(IF(r>=SEQUENCE(,MAX(r)),SEQUENCE(ROWS(r)),NA()),2)))(A2:A8,B2:B8)
F6:F21	F6	=LET(r,B2:B8,INDEX(A2:A8,TOCOL(IF(r>=SEQUENCE(,MAX(r)),SEQUENCE(ROWS(r)),NA()),2)))
Dynamic array formulas.

REPEATBYNUMBER

BatCoder

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock