REPEAT

lrobbo314 · Jan 19, 2023

REPEAT will duplicate an input range by a given multiple of rows and columns

Excel Formula:

=LAMBDA(
    range,vertical_repeats,horizontal_repeats,
    LET(
        r_cnt, ROWS(range),
        c_cnt, COLUMNS(range),
        ro,r_cnt*vertical_repeats,
        co,c_cnt*horizontal_repeats,
        rx,MOD(TRANSPOSE(SEQUENCE(co,ro,0)),r_cnt)+1,
        cx,MAKEARRAY(
            ro,co,
                LAMBDA(
                    r,c,
                    MOD(c-1,c_cnt)+1
                )
            ),
        INDEX(range,rx,cx)))

REPEAT

A

B

C

D

E

F

G

H

I

J

K

L

M

N

2

Single Column

3

A

Repeat Rows:

3

Repeat Rows:

2

4

B

Repeat Columns:

1

Repeat Columns:

4

5

C

Result:

A

Result:

A

6

B

7

C

8

A

9

B

10

C

11

A

12

B

13

C

14

15

Multiple Column

16

W

1

Repeat Rows:

3

Repeat Rows:

2

17

X

2

Repeat Columns:

1

Repeat Columns:

3

18

Y

3

Result:

W

1

Result:

W

1

W

1

W

1

19

Z

4

X

2

X

2

X

2

X

2

20

Y

3

Y

3

Y

3

Y

3

21

Z

4

Z

4

Z

4

Z

4

22

W

1

W

1

W

1

W

1

23

X

2

X

2

X

2

X

2

24

Y

3

Y

3

Y

3

Y

3

25

Z

4

Z

4

Z

4

Z

4

26

W

1

27

X

2

28

Y

3

29

Z

4

Sheet3

Cell Formulas
Range	Formula
E5:E13	E5	=REPEAT(A3:A5,E3,E4)
I5:L10	I5	=REPEAT(A3:A5,I3,I4)
E18:F29	E18	=REPEAT(A16:B19,E16,E17)
I18:N25	I18	=REPEAT(A16:B19,I16,I17)
Dynamic array formulas.

Xlambda · Apr 3, 2023

Hi, another alternative to replace "slow" MAKEARRAY

AREPT(ar,v,[h]) ar:array, v: vertical repeat, [h]: horiz repeat, if omitted=> 1, can handle blanks

Excel Formula:

=LAMBDA(ar, v, [h],
    LET(
        a, IF(ar = "", "", ar),
        r, ROWS(a),
        c, COLUMNS(a),
        INDEX(a, MOD(SEQUENCE(r * MAX(v, 1)) - 1, r) + 1, MOD(SEQUENCE(, c * MAX(h, 1)) - 1, c) + 1)
    )
)

Book1.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

1

2

ar

=AREPT("A",2,3)

<=>

=REPT(B3,SEQUENCE(2,3)^0)

3

A

4

A

5

v,3,h,omitted

v,omitted,h,3

v,3,h,3

6

ar

ar

7

A

a

1

A

a

1

A

a

1

A

a

1

A

a

1

a

8

B

b

2

B

b

2

B

b

2

B

b

2

B

b

2

b

9

C

c

3

C

c

3

C

c

3

C

c

3

C

c

3

c

10

D

d

4

D

d

4

D

d

4

D

d

4

D

d

4

a

11

A

a

1

A

a

1

A

a

1

A

a

1

b

12

B

b

2

B

b

2

B

b

2

B

b

2

c

13

C

c

3

C

c

3

C

c

3

C

c

3

a

14

D

d

4

D

d

4

D

d

4

D

d

4

b

15

A

a

1

A

a

1

A

a

1

A

a

1

c

16

B

b

2

B

b

2

B

b

2

B

b

2

17

blank

C

c

3

C

c

3

C

c

3

C

c

3

v,3,h,omitted

v,omitted,h,3

18

empty string

D

d

4

D

d

4

D

d

4

D

d

4

ar

=AREPT(U19:W19,3)

=AREPT(U19:W19,,3)

19

a

b

c

a

b

c

a

b

c

a

b

c

a

b

c

20

→

v,omitted,h,4

a

b

c

21

ar

=AREPT(B22:D25,,4)

a

b

c

v,3,h,3

22

A

a

1

A

a

1

A

a

1

A

a

1

A

a

1

=AREPT(U19:W19,3,3)

23

B

b

2

B

b

2

B

b

2

B

b

2

B

b

2

a

b

c

a

b

c

a

b

c

24

C

3

C

3

C

3

C

3

C

3

a

b

c

a

b

c

a

b

c

25

D

d

4

D

d

4

D

d

4

D

d

4

D

d

4

a

b

c

a

b

c

a

b

c

26

27

v,3,h,omitted

repeat patterns

28

=AREPT(B22:D25,3)

=AREPT(SEQUENCE(3,4),4,3)

29

A

a

1

2

3

4

1

2

3

4

1

2

3

4

30

B

b

2

5

6

7

8

5

6

7

8

5

6

7

8

31

C

3

9

10

11

12

9

10

11

12

9

10

11

12

32

D

d

4

1

2

3

4

1

2

3

4

1

2

3

4

33

A

a

1

5

6

7

8

5

6

7

8

5

6

7

8

34

B

b

2

9

10

11

12

9

10

11

12

9

10

11

12

35

C

3

1

2

3

4

1

2

3

4

1

2

3

4

36

D

d

4

5

6

7

8

5

6

7

8

5

6

7

8

37

A

a

1

9

10

11

12

9

10

11

12

9

10

11

12

38

B

b

2

1

2

3

4

1

2

3

4

1

2

3

4

39

C

3

5

6

7

8

5

6

7

8

5

6

7

8

40

D

d

4

9

10

11

12

9

10

11

12

9

10

11

12

41

Sheet1

Cell Formulas
Range	Formula
G2,T28,G28,AD22,G21,AD18,Y18,AF6,W6,AA6,G6,L2	G2	=FORMULATEXT(G3)
G3:I4	G3	=AREPT("A",2,3)
L3:N4	L3	=REPT(B3,SEQUENCE(2,3)^0)
G7:R18	G7	=AREPT(B7:D10,3,4)
W7:W15	W7	=AREPT(U7:U9,3)
AA7:AC9	AA7	=AREPT(U7:U9,,3)
AF7:AH15	AF7	=AREPT(U7:U9,3,3)
Y19:AA21	Y19	=AREPT(U19:W19,3)
AD19:AL19	AD19	=AREPT(U19:W19,,3)
G22:R25	G22	=AREPT(B22:D25,,4)
AD23:AL25	AD23	=AREPT(U19:W19,3,3)
G29:I40	G29	=AREPT(B22:D25,3)
T29:AE40	T29	=AREPT(SEQUENCE(3,4),4,3)
Dynamic array formulas.

lrobbo314 · Apr 3, 2023

It's funny. The original post was only a few months ago. But, I can't even remember the last time I used 'MAKEARRAY'.

for all of the insights. Always interested in seeing better/more efficient ways of making these formulas!

REPEAT

lrobbo314

Well-known Member

Xlambda

Well-known Member

lrobbo314

Well-known Member

Forum statistics

Share this page

REPEAT

lrobbo314

Well-known Member

Xlambda

Well-known Member

lrobbo314

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