SREPEAT

SREPEAT(reference,[rows],[columns])
reference
Required. Specifies the function input and can be any single value/cell.
rows
Optional. Specifies the number of rows of repeats for the input and can be either omitted or any positive number greater than or equal to 1.
columns
Optional. Specifies the number of columns of repeats for the input and can be either omitted or any positive number greater than or equal to 1.
SREPEAT is a simple repeating solution that spills repeats, at the specified number of rows and/or columns, of any single value, including errors or blanks, whether or not enclosed in array brackets
R

Rnkhch

Well-known Member
Joined
Apr 28, 2018
Messages
578
Office Version
  1. 365
Platform
  1. Windows
SREPEAT is a simple repeating solution that spills repeats, at the specified number of rows and/or columns, of any single value, including errors or blanks, whether or not enclosed in array brackets


SREPEAT takes three parameters, one required and two optional, as follows:

A) The first parameter, required, specifies the function input and can be any single value/cell

Note that any data type, even errors, can be included as reference (for a full list of all data types, refer to the latest update of XTYPE)

Note that the single input can be in array brackets as well (e.g. {45} or {"book"} or {#DIV/0!})

Note that if more than one value is entered, the function will crash to an #N/A

B) the second parameter, optional, specifies the number of rows of repeats for the input and can be either omitted or any positive number greater than or equal to 1

Note that if this parameter is omitted, the default number of rows will be 1

Note that if a non-integer number is entered, it will be rounded down to the nearest integer

Note that if zero or a number between zero and one is entered, the function will crash to a #CALC! error

Note that the single number of rows can be in array brackets as well (e.g. {12})

Note that if a negative number is entered, the function will crash to a #VALUE! error

Note that if more than one number is entered, the function will crash to an #N/A

Note that if any other value besides a single number is entered, the function will crash to an #N/A

C) the second parameter, optional, specifies the number of columns of repeats for the input and can be either omitted or any positive number greater than or equal to 1

Note that if this parameter is omitted, the default number of columns will be 1

Note that if a non-integer number is entered, it will be rounded down to the nearest integer

Note that if zero or a number between zero and one is entered, the function will crash to a #CALC! error

Note that the single number of columns can be in array brackets as well (e.g. {8})

Note that if a negative number is entered, the function will crash to a #VALUE! error

Note that if more than one number is entered, the function will crash to an #N/A

Note that if any other value besides a single number is entered, the function will crash to an #N/A

Note that both rows and columns can be used simultaneously to generate a 2D spill of repeats


(You can download my template from the link in my signature below, which makes it easier to obtain/test the functions compared to manually pasting from the text/XL2BB)

SREPEAT
Excel Formula: 
=LAMBDA(reference,[rows],[columns],
   LET(
      r,IF(reference="","",reference),
      a,IF(IO(rows),1,rows),
      b,IF(IO(columns),1,columns),
      IF(
         AND(
            IFERROR(ROWS(r),1)=1,
            IFERROR(COLUMNS(r),1)=1,
            ROWS(IFERROR(a,1))=1,
            COLUMNS(IFERROR(a,1))=1,
            ROWS(IFERROR(b,1))=1,
            COLUMNS(IFERROR(b,1))=1,
            ISNUMBER(a),
            ISNUMBER(b)
         ),
         IF(
            SEQUENCE(INDEX(a,1),INDEX(b,1)),
            r,
            ""
         ),
         NA()
      )
   )
)

IO (Shortened form of ISOMITTED)
Excel Formula: 
=LAMBDA(parameter,
   ISOMITTED(parameter)
)


SREPEAT.xlsx
ABCDEFGHIJKLMNOPQRS
1
2#CALC!
3
444444444
54444
64444
7
8bookbookbookbookbookbookbookbook
9bookbookbookbook
10bookbookbookbook
11
12    
13
14
15
16TRUETRUETRUETRUETRUETRUETRUETRUE
17TRUETRUETRUETRUE
18TRUETRUETRUETRUE
19
20FALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE
21FALSEFALSEFALSEFALSE
22FALSEFALSEFALSEFALSE
23
24#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!
25#DIV/0!#DIV/0!#DIV/0!#DIV/0!
26#DIV/0!#DIV/0!#DIV/0!#DIV/0!
27
28#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A
29#N/A#N/A#N/A#N/A
30#N/A#N/A#N/A#N/A
31
32numbertextblank_realblank_formulablank_spaceblank_pastedlogicallogicalerrorerrorerror
33data type:12313233344142162163165
34input:11note  TRUEFALSE#DIV/0!#VALUE!#NAME?
35
3611note    TRUEFALSE#DIV/0!#VALUE!#NAME?
3711note TRUEFALSE#DIV/0!#VALUE!#NAME?
3811note TRUEFALSE#DIV/0!#VALUE!#NAME?
3911note TRUEFALSE#DIV/0!#VALUE!#NAME?
40
41
4223book23 TRUEFALSE#DIV/0!#N/A
4323book23TRUEFALSE#DIV/0!#N/A
44
45
46#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A
47
SREPEAT
Cell Formulas
RangeFormula
B2B2=LAMBDA(reference,[rows],[columns],LET(r,IF(reference="","",reference),a,IF(IO(rows),1,rows),b,IF(IO(columns),1,columns),IF(AND(IFERROR(ROWS(r),1)=1,IFERROR(COLUMNS(r),1)=1,ROWS(IFERROR(a,1))=1,COLUMNS(IFERROR(a,1))=1,ROWS(IFERROR(b,1))=1,COLUMNS(IFERROR(b,1))=1,ISNUMBER(a),ISNUMBER(b)),IF(SEQUENCE(INDEX(a,1),INDEX(b,1)),r,""),NA())))
B4B4=SREPEAT(4)
F4:F6F4=SREPEAT(4,3)
J4:L4J4=SREPEAT(4,,3)
P4:R6P4=SREPEAT(4,3,3)
B8B8=SREPEAT("book")
F8:F10F8=SREPEAT("book",3)
J8:L8J8=SREPEAT("book",,3)
P8:R10P8=SREPEAT("book",3,3)
B12B12=SREPEAT("")
F12:F14F12=SREPEAT("",3)
J12:L12J12=SREPEAT("",,3)
P12:R14P12=SREPEAT("",3,3)
B16B16=SREPEAT(TRUE)
F16:F18F16=SREPEAT(TRUE,3)
J16:L16J16=SREPEAT(TRUE,,3)
P16:R18P16=SREPEAT(TRUE,3,3)
B20B20=SREPEAT(FALSE)
F20:F22F20=SREPEAT(FALSE,3)
J20:L20J20=SREPEAT(FALSE,,3)
P20:R22P20=SREPEAT(FALSE,3,3)
B24B24=SREPEAT(#DIV/0!)
F24:F26F24=SREPEAT(#DIV/0!,3)
J24:L24J24=SREPEAT(#DIV/0!,,3)
P24:R26P24=SREPEAT(#DIV/0!,3,3)
B28B28=SREPEAT(#N/A)
F28:F30F28=SREPEAT(#N/A,3)
J28:L28J28=SREPEAT(#N/A,,3)
P28:R30P28=SREPEAT(#N/A,3,3)
E34E34=IF(1,"")
J34J34=#DIV/0!
B36:L39B36=SREPEAT(B34,4)
B42:B43,D42:D43B42=SREPEAT({23},{2})
C42:C43C42=SREPEAT({"book"},{2})
E42:E43E42=SREPEAT({""},{2})
F42:F43F42=SREPEAT({TRUE},{2})
G42:G43G42=SREPEAT({FALSE},{2})
H42:H43H42=SREPEAT({#DIV/0!},{2})
I42:I43I42=SREPEAT({#N/A},{2})
B46B46=SREPEAT({1,2})
D46D46=SREPEAT({1,"book"})
F46F46=SREPEAT({#DIV/0!,2})
H46H46=SREPEAT({#DIV/0!,#N/A})
J46J46=SREPEAT({#DIV/0!,#DIV/0!})
L46L46=SREPEAT(45,{1,2})
N46N46=SREPEAT(45,#DIV/0!)
P46P46=SREPEAT(45,,"")
Dynamic array formulas.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,883
Messages
6,175,167
Members
452,615
Latest member
bogeys2birdies

Share this page

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