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.

st001 · Jan 7, 2023

I think you are right, one of the XLOOKUP rows wasn't found in the lookup array, so I'm going to try and return a return array of [0] with the same with of the return array
So I need to include some error management in the LAMBDA (fn)
Only concern / question here is, for a vertical column lookup of height 600, and return array width, will Excel be ok
- using RBROW, since it's using things like REDUCE and VSTACK

I have a file with about 14 such sheets..

Wondered if Microsoft thought about why BYROW(.... (XLOOKUP....)) pattern doesn't work - to avoid the need to have to create a custom function

Will update later with how I get one.

Xlambda · Jan 7, 2023

st001 said:
Only concern / question here is, for a vertical column lookup of height 600, and return array width, will Excel be ok
- using RBROW, since it's using things like REDUCE and VSTACK

Any iterative process, built-in or custom made is time consuming.
6000 (six thousand) rows need 2-3 seconds to stack.
I have other 2 functions that can spill results by row: CBYROW and TBYROW (Column Byrow and Textsplit Byrow)
RBYROW is the most versatile of all, it's the only one capable of expanding rows, check =RBYROW(SEQUENCE(5),LAMBDA(x,SEQUENCE(2,x)))
CBYROW spills by row but iterates by nr of columns, not by nr. of rows.
TBYROW uses native BYROW, joins results, and then calls other custom-made function to split the joined result "in bulk" (requires an extra function) but is lighting fast.
So if you need more speed write me here, AUNIQUE where I have some posts on the matter.

st001 said:
Wondered if Microsoft thought about why BYROW(.... (XLOOKUP....)) pattern doesn't work - to avoid the need to have to create a custom function

Will update later with how I get one.

BYROW returns a single value /row by design. If a function delivers more than 1 result/row => error.
Hope that helps!!

st001 · Jan 7, 2023

Thanks Xlambda!

I included some error management in the XLOOKUP argument

I'm using RBYROW (your custom function)...e.g.:

RBYROW([column_of_lookup_values], LAMBDA([single lookup_value, XLOOKUP([single lookup_value], [vertical lookup_array], [return_table (height x width)]

1. The column of lookup values is about 600 (for my biggest dataset)
2. The return table is about 300 (for my biggest dataset)

So I get a return array of dimension 600 x 300 (with a single formula - which I can trust a lot more!)

But, if TBYROW might be faster, or more robust, I would be interested in learning how to use it.

Best,

ST

Xlambda · Jan 7, 2023

st001 said:
So I get a return array of dimension 600 x 300 (with a single formula - which I can trust a lot more!)

But, if TBYROW might be faster, or more robust, I would be interested in learning how to use it.

600x300 is nothing, no problem with that, it should be instant.
RBYROW is super robust because it is super minimalistic. Write any formula for a single row to check that it works for all situations, assemble it in RBYROW and its bullet proof.
I have on pending so much work to publish, including a full study for arrays that will include TBYROW and lot lot more, but editing the way I want to be presented is super time consuming and my spare time is close to 0

LogChief · Dec 24, 2023

I haven't seen this version posted. Just use Xlookup's 4th argument to find exact match or next largest value:

Excel Formula:

=LAMBDA(data, repeat, XLOOKUP(SEQUENCE(SUM(repeat)), SCAN(0, repeat, LAMBDA(a, v, a + v)), data, , 1))

lrobbo314 · Dec 24, 2023

I saw a cool trick to duplicate cell values. This made me think of it, so I thought I would share it.

Book1

O

P

Q

R

1

Product

Repeat

Result (range 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

Sheet1

Cell Formulas
Range	Formula
R2:R16	R2	=TEXTSPLIT(TEXTJOIN("~",,MAP(O2:O5,P2:P5,LAMBDA(x,y,TEXTJOIN("~",,TOCOL(IF(SEQUENCE(,y),TOCOL(x))))))),,"~")
Dynamic array formulas.

Xlambda · Dec 24, 2023

LogChief said:
I haven't seen this version posted. Just use Xlookup's 4th argument to find exact match or next largest value:

Excel Formula:

=LAMBDA(data, repeat, XLOOKUP(SEQUENCE(SUM(repeat)), SCAN(0, repeat, LAMBDA(a, v, a + v)), data, , 1))

Hi @LogChief, probably you've missed it.

Check second post of this thread, on Sep 18 2022, here: REPEATBYNUMBER
It has 2 lambdas solutions, the one with SCAN and the 2nd one, a classic which by far is the one to be used in such a case, more efficient than XLOOKUP. XLOOKUP when it is not binary search is dead slow.
This is how simple a lambda looks with 2 variables instead of one array for both.

Excel Formula:

=LAMBDA(a,r,INDEX(a,TOCOL(IF(r>=SEQUENCE(,MAX(r)),SEQUENCE(ROWS(r)),NA()),2)))

Hi @lrobbo314,
Great formula, inspired by that one came up with a solution without MAP (do not forget though that TEXTJOIN, CONCAT are limited in chars 32767)

Excel Formula:

=DROP(TEXTSPLIT(CONCAT(REPT(val&"|",rept)),,"|"),-1)

examples for both scenarios:

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

1

2

=LAMBDA(a,r,INDEX(a,TOCOL(IF(r>=SEQUENCE(,MAX(r)),SEQUENCE(ROWS(r)),NA()),2)))(G5:G8,H5:H8)

3

Aaaaaa

5

Aaaaaa

4

Bbbbbbb

4

Aaaaaa

=LAMBDA(a,r,INDEX(a,TOCOL(IF(r>=SEQUENCE(,MAX(r)),SEQUENCE(ROWS(r)),NA()),2)))(G5:G8,H5:H8)

5

Ccccc

2

Aaaaaa

5

Aaaaaa

6

Ddddd

4

Aaaaaa

Bbbbbbb

4

Aaaaaa

7

Aaaaaa

Ccccc

0

Aaaaaa

8

Bbbbbbb

Ddddd

4

Aaaaaa

9

Bbbbbbb

Aaaaaa

10

Bbbbbbb

11

Bbbbbbb

12

Ccccc

Bbbbbbb

13

Ccccc

Bbbbbbb

14

Ddddd

15

Ddddd

16

Ddddd

17

Ddddd

18

19

=DROP(TEXTSPLIT(CONCAT(REPT(B20:B23&"|",C20:C23)),,"|"),-1)

20

Aaaaaa

5

Aaaaaa

21

Bbbbbbb

4

Aaaaaa

=DROP(TEXTSPLIT(CONCAT(REPT(G22:G25&"|",H22:H25)),,"|"),-1)

22

Ccccc

2

Aaaaaa

5

Aaaaaa

23

Ddddd

4

Aaaaaa

Bbbbbbb

4

Aaaaaa

24

Aaaaaa

Ccccc

0

Aaaaaa

25

Bbbbbbb

Ddddd

4

Aaaaaa

26

Bbbbbbb

Aaaaaa

27

Bbbbbbb

28

Bbbbbbb

29

Ccccc

Bbbbbbb

30

Ccccc

Bbbbbbb

31

Ddddd

32

Ddddd

33

Ddddd

34

Ddddd

35

Sheet1

Cell Formulas
Range	Formula
E2	E2	=FORMULATEXT(J5)
E3:E17,J5:J17	E3	=LAMBDA(a,r,INDEX(a,TOCOL(IF(r>=SEQUENCE(,MAX(r)),SEQUENCE(ROWS(r)),NA()),2)))(B3:B6,C3:C6)
J4,J21,E19	J4	=FORMULATEXT(J5)
E20:E34,J22:J34	E20	=DROP(TEXTSPLIT(CONCAT(REPT(B20:B23&"\|",C20:C23)),,"\|"),-1)
Dynamic array formulas.

Dave Patton · Dec 25, 2023

Based on lrobbo314's post #19.
A little more concise.

Complex Formulas.xlsm

A

B

C

F

1

Product

Repeat

2

Apple

5

Apple

3

Banana

4

Apple

4

Cherry

2

Apple

5

Strawberry

1

Apple

6

Apple

7

Banana

8

Banana

9

Banana

10

Banana

11

Cherry

12

Cherry

13

Strawberry

14

1b

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

Xlambda · Dec 27, 2023

Hi @Dave Patton,
Please pardon me, a stupid question, why to use SUM(a,b) instead of a+b.?
And also why use offset'ed arrays? (Product array is A2:A5 and Repeat array is B2:B5. Your formula uses 3 arrays B2:B5, B1:B5, A2:A6.)

Dave Patton · Dec 27, 2023

"why to use SUM(a,b) instead of a+b.?"

N.B. There are great ideas in the suggestions; I just edited one of the suggestions.

For blank or 0, we do not want to show the item.
a+b will error if one of components is text.
Sum will ignore the text but possibly more logical is N(b) that will yield a value of 0.

Complex Formulas.xlsm

A

B

C

F

1

Product

Repeat

2

Apple

5

Apple

3

Banana

4

Apple

4

Cherry

2

Apple

5

Strawberry

1

Apple

6

Apple

7

Banana

8

Banana

9

Banana

10

Banana

11

Cherry

12

Cherry

13

Strawberry

14

1b

Cell Formulas
Range	Formula
F2:F13	F2	=XLOOKUP(SEQUENCE(SUM(rB)),SCAN(1,rB,LAMBDA(a,b,a+N(b))),A2:A6,,-1)
Dynamic array formulas.

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

REPEATBYNUMBER

smozgur

BatCoder

st001

New Member

Xlambda

Well-known Member

st001

New Member

Xlambda

Well-known Member

LogChief

New Member

lrobbo314

Well-known Member

Xlambda

Well-known Member

Dave Patton

Well-known Member

Xlambda

Well-known Member

Dave Patton

Well-known Member

Forum statistics

Share this page

REPEATBYNUMBER

BatCoder

New Member

Well-known Member

New Member

Well-known Member

New 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