Disappearing REPEAT() function

[GSM54321]

In late 2022 (probably Oct or Nov) I discovered REPEAT() as a function in Excel. It would generate a new array where the input range was duplicated with the specified number of vertical and horizontal repeats. For example, =REPEAT(A1:B1,3,4) would generate an array of 6 rows and 4 columns where there were interlaced rows with the values A1 and B1 respectively, repeated in 4 columns where the values of the 2nd, 3rd, and 4th columns were equal to the values in the first column. I was using Office Insider channel at the time (unsure of what build) and incorporated REPEAT() into some of my spreadsheets.

I noticed it was no longer available while using OFFICE INSIDER Version 2301 Build 16026.20002. Trying to use REPEAT() gives a #NAME error. (FWIW, REPT() is available, but it's obviously a completely different task.) I'm using 2302 INSIDER Build 16110.20000 and it remains an unavailable function.

Multiple internet searches for only had info on the REPT() function. I was able to do what I needed with a combination of INDEX and sequence, but it was weird to have something that was a built-in function disappear. It was definitely not a user-defined function (nothing in Name Manager as either LAMBDA or UDF.)

Funny thing, my work computer still recognizes REPEAT() -- note the attached screenshot --and functions as the tips imply. My work computer has MS 365 Apps for enterprise Version 2211 (Build 15831.20208) and runs the calculations using REPEAT() as originally written.

This seems very strange. Anyone have any idea what's going on?

Thanks for any insights,

GSM

 

[Fluff]

As far as I know there has never been a Repeat function in Excel. I suspect that it is a UDF possibly in an add-in.

 

[lrobbo314]

Here is a lambda function that replicates that function, as far as I can tell.

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:	3
4	B			Repeat Columns:	1			Repeat Columns:	2
5	C			Result:	A			Result:	A	A
6					B				B	B
7					C				C	C
8					A				A	A
9					B				B	B
10					C				C	C
11					A				A	A
12					B				B	B
13					C				C	C
14
15	Multiple Column
16	X	1		Repeat Rows:	3			Repeat Rows:	3
17	Y	2		Repeat Columns:	1			Repeat Columns:	3
18	Z	3		Result:	X	1		Result:	X	1	X	1	X	1
19					Y	2			Y	2	Y	2	Y	2
20					Z	3			Z	3	Z	3	Z	3
21					X	1			X	1	X	1	X	1
22					Y	2			Y	2	Y	2	Y	2
23					Z	3			Z	3	Z	3	Z	3
24					X	1			X	1	X	1	X	1
25					Y	2			Y	2	Y	2	Y	2
26					Z	3			Z	3	Z	3	Z	3
Sheet3

Cell Formulas
Range	Formula
E5:E13	E5	=REPEAT(A3:A5,E3,E4)
I5:J13	I5	=REPEAT(A3:A5,I3,I4)
E18:F26	E18	=REPEAT(A16:B18,E16,E17)
I18:N26	I18	=REPEAT(A16:B18,I16,I17)
Dynamic array formulas.

=LAMBDA(
    range,r_mult,c_mult,
    LET(
        ro,ROWS(range)*r_mult,
        co,COLUMNS(range)*c_mult,
        rx,MOD(TRANSPOSE(SEQUENCE(co,ro,0)),3)+1,
        cx,MAKEARRAY(
            ro,co,
                LAMBDA(
                    r,c,
                    MOD(c-1,COLUMNS(range))+1
                )
            ),
        INDEX(range,rx,cx)))

 

[lrobbo314]

Small edit to the 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)))

 

[GSM54321]

As far as I know there has never been a Repeat function in Excel. I suspect that it is a UDF possibly in an add-in.

Thank you, your guess is correct.

It appears to be a UDF in FastExcel V4. I noticed that some of the modules in FastExcel V4 were inactivated on the computer with the INSIDER version of 365. When I reactivated them the function REPEAT() is available again.

Understandably, the project for the FastExcel add-ins are unviewable, so I am unable to search for UDFs in the add-in.

As a general question, is there an easy way to find out what functions are UDF so that I can be aware of what may not be available if other users don't have the add-in? I guess I could always search the alphabetical list of Excel functions on MS support page when trying a new function.

GSM

 

[GSM54321]

Small edit to the 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)))

Thank you lrobbo314 for taking the effort to write and then post this lambda. This is great.

This is a much simpler solution with better generalization than the fix I came up with, which also used MOD, INDEX, SEQUENCE but organized the array with more clumsy implementations of HSTACK and VSTACK.

much appreciated,

GSM

 

[Fluff]

As a general question, is there an easy way to find out what functions are UDF

Not that I'm aware of.