MREPEAT

[Rnkhch]

MREPEAT is a powerful repeating solution that spills array elements of any data type at the specified number of counts with the ability to insert blanks and filter out specific elements as well as stacking


MREPEAT takes three parameters, one required and two optional, as follows:
A) The first parameter, required, specifies the function input and can be any 1D array/range

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 elements entered in this parameter can be only one-dimensional arrays; entering two-dimensional arrays will cause the function to return a #VALUE! error

Note that entering elements in column format (i.e. separated with semicolons) (e.g. {3;"book";FALSE} will result in an output which is horizontal; however, this output is NOT equivalent to the transposed version of row format (i.e. counts separated with commas) as the function practically linearizes the output by scanning vertically down the stacked version of the output (see section C below) whether the counts are entered in row or column format. I intentionally coded the output this way as an extra feature for more diverse output options. If stack is turned on, however, the two outputs will be the transposed version of each other

B) The second parameter, optional, specifies the count(s) of the element(s) in "reference" and can be either omitted or any integer(s)

Note that if more than one number needs to be entered, they should be included in array brackets "{}" per native syntax

Note that there is a one-to-one correspondence between the numbers in "counts" and the elements of the "reference"

Note that if this parameter is left omitted, the default count of 1 will be applied to all elements of the reference

Note that if fewer numbers than the count of the reference elements are entered, only those elements of the reference that have corresponding numbers in "counts" will be repeated and the remaining reference elements will be ignored; thus, if additional numbers are later added to "counts", the reference elements corresponding to these new numbers will start to appear in the output spill

Note that if more numbers than the count of the reference elements are entered, the numbers past the last point of correspondence to the reference elements will be ignored; thus, if more elements are later added to the reference, these new elements will be repeated in the output spill as long as they are in correspondence to a number in "counts"

Note that if zero is used for any "reference" element, that element will be omitted from the output spill and not repeated

Note that non-integer values will be rounded down to the nearest integer

Note that if only zero(s) or number(s) between 0 and 1 are included in this parameter, the output will be a #CALC! error

Note that including negative number(s) in this parameter will result in sequence(s) of blanks with count(s) corresponding to the absolute value(s) of the negative number(s); thus, if only one or more negative numbers are included, the output will be a series of blanks

Note that the function linearizes the numbers in this parameter, so they can technically be entered either in row format (i.e. separated with commas) or column format (i.e. separated with semicolons)

Note that if anything other than numbers is entered in this parameter, the function will crash to an #N/A

C) The third parameter, optional, specifies the output format of the function and takes two general arguments:
0 or omitted or FALSE, for linear output
1 or TRUE or any number other than 0, for stacked output

Note that besides being omitted, only one real number or TRUE/FALSE can be entered in this parameter; entering more than one number or logical will cause the function to crash to an #N/A

Note that if anything other than the specified allowed arguments is entered, the function will crash to an #N/A


(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)

MREPEAT

=LAMBDA(reference,[counts],[stack],
   LET(
      aa,IFERROR(COLUMNS(reference),1),
      ac,IFERROR(ROWS(reference),1),
      ag,TOROW(IF(IO(counts),SEQUENCE(,MAX(aa,ac),,0),counts)),
      ae,aa>=ac,
      af,COLUMNS(ag),
      ab,IF(ae,af,1),
      ad,IF(ae,1,af),
      v,IF(
         ab<aa,
         HSTACK(counts,SEQUENCE(1,aa-ab,0,0)),
         IF(
            ad<ac,
            VSTACK(TOCOL(counts),SEQUENCE(ac-ad,1,0,0)),
            IF(ae,ag,TRANSPOSE(ag))
         )
      ),
      n,IF(
         ab>aa,
         CHOOSECOLS(v,SEQUENCE(,aa)),
         IF(ad>ac,CHOOSEROWS(v,SEQUENCE(ac)),v)
      ),
      z,IF(reference="","",reference),
      a,IF(n<0,"",IF(AND(aa=1,ac=1),CHOOSECOLS(z,1),z)),
      s,IF(IO(stack),0,IF(AND(ROWS(IFERROR(stack,1))=1,COLUMNS(IFERROR(stack,1))=1),INDEX(stack,1),NA())),
      m,ROWS(n),
      l,COLUMNS(n),
      o,IF(l>=m,1,2),
      r,SWITCH(o,1,SEQUENCE(MAX(ABS(n))),SEQUENCE(,MAX(ABS(n)))),
      c,SWITCH(o,1,SEQUENCE(,l),SEQUENCE(m)),x,r<=ABS(INDEX(n,c)),
      k,ROWS(a),h,COLUMNS(a),b,IF(h>1,1,IF(k=1,1,0)),
      d,IF(b,h,k),
      p,FILTER(IF(b,SEQUENCE(,h),SEQUENCE(k)),n<>0),
      q,IF(x,INDEX(a,c),""),
      y,IF(b,CHOOSECOLS(q,p),CHOOSEROWS(q,p)),
      u,IF(b,CHOOSECOLS(x,p),CHOOSEROWS(x,p)),
      f,COLUMNS(y),
      e,ROWS(y),
      g,SEQUENCE(e*f),
      i,MOD(g-1,e)+1,
      j,INT((g-1)/e)+1,
      t,FILTER(INDEX(y,i,j),INDEX(u,i,j)),
      w,IF(s,y,SWITCH(o,1,t,TRANSPOSE(t))),
      IF(
         AND(
            OR(ISNUMBER(s),ISLOGICAL(s)),
            ISNUMBERS(counts,TRUE)
         ),
         w,
         NA()
      )
   )
)

ISNUMBERS (Returns TRUE if ALL elements in the reference are numbers and FALSE otherwise)

=LAMBDA(reference,[if_omitted],
   IF(
      IO(reference),
      IF(IO(if_omitted),FALSE,if_omitted),
      IF(PRODUCT(ISNUMBER(reference)*1),TRUE,FALSE)
   )
)

IO (shortened form of ISOMITTED)

=LAMBDA(parameter,
   ISOMITTED(parameter)
)

MREPEAT.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1
2		#CALC!
3
4		34		book				TRUE		FALSE		#DIV/0!
5
6		34		book				TRUE		FALSE		#DIV/0!
7
8		34		book				TRUE		FALSE		#DIV/0!
9
10		34		book				TRUE		FALSE		#DIV/0!
11
12		45		45		45		45		45	note	#REF!	TRUE		45		45
13		note		45		45		45		45	note	#REF!	TRUE		45		45
14		#REF!		45		45		45		45		#REF!	TRUE		45		45
15		TRUE		note		note		note				#REF!	TRUE		note		note
16				note		note		note					TRUE		note		note
17				#REF!				TRUE							#REF!		#REF!
18				#REF!				TRUE		45	note		TRUE		#REF!		#REF!
19				#REF!				TRUE		45	note		TRUE		#REF!		#REF!
20				#REF!				TRUE		45			TRUE		#REF!		#REF!
21				TRUE		TRUE		TRUE					TRUE		TRUE
22				TRUE		TRUE							TRUE		TRUE
23				TRUE		TRUE									TRUE
24				TRUE		TRUE				45	note	TRUE			TRUE
25				TRUE		TRUE				45	note	TRUE			TRUE
26										45		TRUE
27												TRUE
28												TRUE
29
30		45	note	#REF!	TRUE	45	note	#REF!	TRUE	45	#REF!	TRUE	#REF!	TRUE	TRUE
31
32
33		book	FALSE	#DIV/0!	0	TRUE														book
34																				book
35		book	FALSE	#DIV/0!	0	TRUE	book	FALSE	#DIV/0!	0	TRUE	book	FALSE	#DIV/0!	TRUE	FALSE	#DIV/0!	FALSE		book
36																				FALSE
37		book	FALSE		0	TRUE	book	FALSE		0	TRUE	book	FALSE		TRUE	FALSE		FALSE		FALSE
38																				FALSE
39		book	FALSE	0	TRUE	book	FALSE	0	TRUE	book	FALSE	TRUE	FALSE	FALSE						FALSE
40																				FALSE
41		book	book	book				book	book	book				book	book	book				#DIV/0!
42		FALSE	FALSE	FALSE	FALSE	FALSE		FALSE	FALSE	FALSE	FALSE	FALSE		FALSE	FALSE	FALSE	FALSE	FALSE		#DIV/0!
43		#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!									0	0					#DIV/0!
44		0	0					0	0					TRUE	TRUE	TRUE				#DIV/0!
45		TRUE	TRUE	TRUE				TRUE	TRUE	TRUE										0
46																				0
47		book	FALSE	#DIV/0!	0	TRUE	book	FALSE	#DIV/0!	0	TRUE	book	FALSE	#DIV/0!	TRUE	FALSE	#DIV/0!	FALSE		TRUE
48																				TRUE
49		book	FALSE	#DIV/0!	0	book	FALSE	#DIV/0!	0	book	FALSE	#DIV/0!	FALSE	#DIV/0!	FALSE					TRUE
50
51
52	data type	1	2	31	32	33	34	41	42	162	164
53		number	text	blank_real	blank_formula	blank_space	blank_pasted	logical	logical	error	error
54		27	book					TRUE	FALSE	#DIV/0!	#REF!
55		27	book					TRUE	FALSE	#DIV/0!	#REF!
56		27	book					TRUE	FALSE	#DIV/0!	#REF!
57
58
59		27	book					TRUE	FALSE	#DIV/0!	#REF!	27	book					TRUE	FALSE	#DIV/0!	#REF!
60
61		27	27		27		27	book					TRUE	FALSE	#DIV/0!	#REF!
62		book	book		27		27	book					TRUE	FALSE	#DIV/0!	#REF!
63					book
64					book
65
66												#VALUE!
67		TRUE	TRUE
68		FALSE	FALSE
69		#DIV/0!	#DIV/0!
70		#REF!	#REF!
71
72
73					TRUE
74					TRUE
75					FALSE
76					FALSE
77					#DIV/0!
78					#DIV/0!
79					#REF!
80					#REF!
81
MREPEAT

Cell Formulas
Range	Formula
B2	B2	=LAMBDA(reference,[counts],[stack],LET(aa,IFERROR(COLUMNS(reference),1),ac,IFERROR(ROWS(reference),1),ag,TOROW(IF(IO(counts),SEQUENCE(,MAX(aa,ac),,0),counts)),ae,aa>=ac,af,COLUMNS(ag),ab,IF(ae,af,1),ad,IF(ae,1,af),v,IF(ab<aa,HSTACK(counts,SEQUENCE(1,aa-ab,0,0)),IF(ad<ac,VSTACK(TOCOL(counts),SEQUENCE(ac-ad,1,0,0)),IF(ae,ag,TRANSPOSE(ag)))),n,IF(ab>aa,CHOOSECOLS(v,SEQUENCE(,aa)),IF(ad>ac,CHOOSEROWS(v,SEQUENCE(ac)),v)),z,IF(reference="","",reference),a,IF(n<0,"",IF(AND(aa=1,ac=1),CHOOSECOLS(z,1),z)),s,IF(IO(stack),0,IF(AND(ROWS(IFERROR(stack,1))=1,COLUMNS(IFERROR(stack,1))=1),INDEX(stack,1),NA())),m,ROWS(n),l,COLUMNS(n),o,IF(l>=m,1,2),r,SWITCH(o,1,SEQUENCE(MAX(ABS(n))),SEQUENCE(,MAX(ABS(n)))),c,SWITCH(o,1,SEQUENCE(,l),SEQUENCE(m)),x,r<=ABS(INDEX(n,c)),k,ROWS(a),h,COLUMNS(a),b,IF(h>1,1,IF(k=1,1,0)),d,IF(b,h,k),p,FILTER(IF(b,SEQUENCE(,h),SEQUENCE(k)),n<>0),q,IF(x,INDEX(a,c),""),y,IF(b,CHOOSECOLS(q,p),CHOOSEROWS(q,p)),u,IF(b,CHOOSECOLS(x,p),CHOOSEROWS(x,p)),f,COLUMNS(y),e,ROWS(y),g,SEQUENCE(e*f),i,MOD(g-1,e)+1,j,INT((g-1)/e)+1,t,FILTER(INDEX(y,i,j),INDEX(u,i,j)),w,IF(s,y,SWITCH(o,1,t,TRANSPOSE(t))),IF(AND(OR(ISNUMBER(s),ISLOGICAL(s)),ISNUMBERS(counts,TRUE)),w,NA())))
B4	B4	=MREPEAT(34)
D4	D4	=MREPEAT("book")
F4	F4	=MREPEAT("")
H4	H4	=MREPEAT(TRUE)
J4	J4	=MREPEAT(FALSE)
L4	L4	=MREPEAT(#DIV/0!)
B6	B6	=MREPEAT(34,1)
D6	D6	=MREPEAT("book",1)
F6	F6	=MREPEAT("",1)
H6	H6	=MREPEAT(TRUE,1)
J6	J6	=MREPEAT(FALSE,1)
L6	L6	=MREPEAT(#DIV/0!,1)
B8	B8	=MREPEAT(34,{1})
D8	D8	=MREPEAT("book",{1})
F8	F8	=MREPEAT("",{1})
H8	H8	=MREPEAT(TRUE,{1})
J8	J8	=MREPEAT(FALSE,{1})
L8	L8	=MREPEAT(#DIV/0!,{1})
B10	B10	=MREPEAT({34},{1})
D10	D10	=MREPEAT({"book"},{1})
F10	F10	=MREPEAT({""},{1})
H10	H10	=MREPEAT({TRUE},{1})
J10	J10	=MREPEAT({FALSE},{1})
L10	L10	=MREPEAT({#DIV/0!},{1})
B12:B15	B12	=MREPEAT({45,"note",#REF!,TRUE})
D12:D25	D12	=MREPEAT({45,"note",#REF!,TRUE},{3,2,4,5})
F12:F25	F12	=MREPEAT({45,"note",#REF!,TRUE},{3,2,-4,5})
H12:H21	H12	=MREPEAT({45,"note",#REF!,TRUE},{3,2,0,5})
J12:M16	J12	=MREPEAT({45,"note",#REF!,TRUE},{3,2,4,5},1)
O12:O25	O12	=MREPEAT({45,"note",#REF!,TRUE},{3,2,4,5,6})
Q12:Q20	Q12	=MREPEAT({45,"note",#REF!,TRUE},{3,2,4})
J18:M22	J18	=MREPEAT({45,"note",#REF!,TRUE},{3,2,-4,5},1)
J24:L28	J24	=MREPEAT({45,"note",#REF!,TRUE},{3,2,0,5},1)
B30:O30	B30	=MREPEAT({45;"note";#REF!;TRUE},{3,2,4,5})
B33:F33	B33	=MREPEAT({"book";FALSE;#DIV/0!;0;TRUE})
T33:T49	T33	=MREPEAT({"book",FALSE,#DIV/0!,0,TRUE},{3,5,4,2,3})
B35:R35	B35	=MREPEAT({"book";FALSE;#DIV/0!;0;TRUE},{3,5,4,2,3})
B37:R37	B37	=MREPEAT({"book";FALSE;#DIV/0!;0;TRUE},{3,5,-4,2,3})
B39:N39	B39	=MREPEAT({"book";FALSE;#DIV/0!;0;TRUE},{3,5,0,2,3})
B41:F45	B41	=MREPEAT({"book";FALSE;#DIV/0!;0;TRUE},{3,5,4,2,3},1)
H41:L45	H41	=MREPEAT({"book";FALSE;#DIV/0!;0;TRUE},{3,5,-4,2,3},1)
N41:R44	N41	=MREPEAT({"book";FALSE;#DIV/0!;0;TRUE},{3,5,0,2,3},1)
B47:R47	B47	=MREPEAT({"book";FALSE;#DIV/0!;0;TRUE},{3,5,4,2,3,4})
B49:O49	B49	=MREPEAT({"book";FALSE;#DIV/0!;0;TRUE},{3,5,4,2})
E54	E54	=IF(1,"")
J54	J54	=1/0
B55:K56	B55	=MREPEAT(B54,2)
B59:U59	B59	=MREPEAT(TRANSPOSE(B54:K54),{2,2,2,2,2,2,2,2,2,2})
B61:C70	B61	=MREPEAT(TRANSPOSE(B54:K54),{2,2,2,2,2,2,2,2,2,2},1)
E61:E80	E61	=MREPEAT(B54:K54,{2,2,2,2,2,2,2,2,2,2})
G61:P62	G61	=MREPEAT(B54:K54,{2,2,2,2,2,2,2,2,2,2},1)
L66	L66	=MREPEAT()
N66	N66	=MREPEAT(,)
P66:P68	P66	=MREPEAT(,3)
Dynamic array formulas.