Remove unwanted #N/A errors in this LAMBDA/REDUCE formula

[Rnkhch]

Hello,

Here is a complex vstacking question I came up with the following formula, based on LAMBDA/REDUCE, to spill multiple vstacks of the input array side by side. (I came up with another method as well, but dropped it as it was too slow on large sizes.)

=LET(
   r,{#DIV/0!,6,TRUE;"book",FALSE,#N/A;79,"text",14},
   b,{2,1,3,4,2},
   c,ROWS(r),
   d,COLUMNS(r),
   e,COLUMNS(b),
   v,LAMBDA(n,INDEX(r,MOD(SEQUENCE(n*c,1,0),c)+1,SEQUENCE(1,d))),
   REDUCE("",SEQUENCE(1,e),LAMBDA(x,i,HSTACK(x,v(INDEX(b,i)))))
)

This formula takes a reference input array and an array of 1 or more numbers specifying the intended numbers of side-by-side vstacks, e.g. {2,3,1,4} which generates four sets of vstacks in the sizes of 2, 3, 1, and 4.

However, the formula outputs two groups of #N/A errors (not in the input array) which I have highlighted in red and blue in the XL2BB.

There are also two additional features that I have so far been unable to add to my formula, which are a) filtering out any stacks from the output corresponding to zero values in the vstacks array (e.g. {2,4,0,1,3} which should output 4 sets of vstacks, not 5); and b) outputting blank vstacks (highlighted in green) for any negative numbers in the vstack array (e.g. {2,4,-6,1,3} which should include a 6-repeat blank vstack, (so this array would output 5 sets of vstacks, but the middle set would be 6 repeats of blank cells instead of the input array)

Q1. The errors in the first column are easy to remove by DROP. But how could I modify the formula so it would not generate this first unwanted column?

Q2. The other #N/A errors are in areas that are supposed to be blank in the spill. How can I get rid of these #N/As without removing any errors from the input?

Q3. How can I filter out zeros from the vstacks array, and how can I substitute the input array with blanks for any negative numbers in the vstacks array?

Q4. Is this the best (i.e. the most efficient for large data sizes and/or large number of specified vstacks) formula for generating the spill of multiple vstacks? Please share any other/better methods

I'm gonna make a LAMBDA out of this

Thanks for any input

Sheet1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
68
69		vstacks:	{2,1,3,4,2}
70			#DIV/0!	6	TRUE	#DIV/0!	6	TRUE	#DIV/0!	6	TRUE	#DIV/0!	6	TRUE	#DIV/0!	6	TRUE
71		#N/A	book	FALSE	#N/A	book	FALSE	#N/A	book	FALSE	#N/A	book	FALSE	#N/A	book	FALSE	#N/A
72		#N/A	79	text	14	79	text	14	79	text	14	79	text	14	79	text	14
73		#N/A	#DIV/0!	6	TRUE	#N/A	#N/A	#N/A	#DIV/0!	6	TRUE	#DIV/0!	6	TRUE	#DIV/0!	6	TRUE
74		#N/A	book	FALSE	#N/A	#N/A	#N/A	#N/A	book	FALSE	#N/A	book	FALSE	#N/A	book	FALSE	#N/A
75		#N/A	79	text	14	#N/A	#N/A	#N/A	79	text	14	79	text	14	79	text	14
76		#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#DIV/0!	6	TRUE	#DIV/0!	6	TRUE	#N/A	#N/A	#N/A
77		#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	book	FALSE	#N/A	book	FALSE	#N/A	#N/A	#N/A	#N/A
78		#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	79	text	14	79	text	14	#N/A	#N/A	#N/A
79		#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#DIV/0!	6	TRUE	#N/A	#N/A	#N/A
80		#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	book	FALSE	#N/A	#N/A	#N/A	#N/A
81		#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	79	text	14	#N/A	#N/A	#N/A
82
83		vstacks:	{2,1,3,4,2}
84		#DIV/0!	6	TRUE	#DIV/0!	6	TRUE	#DIV/0!	6	TRUE	#DIV/0!	6	TRUE	#DIV/0!	6	TRUE
85		book	FALSE	#N/A	book	FALSE	#N/A	book	FALSE	#N/A	book	FALSE	#N/A	book	FALSE	#N/A
86		79	text	14	79	text	14	79	text	14	79	text	14	79	text	14
87		#DIV/0!	6	TRUE	#N/A	#N/A	#N/A	#DIV/0!	6	TRUE	#DIV/0!	6	TRUE	#DIV/0!	6	TRUE
88		book	FALSE	#N/A	#N/A	#N/A	#N/A	book	FALSE	#N/A	book	FALSE	#N/A	book	FALSE	#N/A
89		79	text	14	#N/A	#N/A	#N/A	79	text	14	79	text	14	79	text	14
90		#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#DIV/0!	6	TRUE	#DIV/0!	6	TRUE	#N/A	#N/A	#N/A
91		#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	book	FALSE	#N/A	book	FALSE	#N/A	#N/A	#N/A	#N/A
92		#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	79	text	14	79	text	14	#N/A	#N/A	#N/A
93		#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#DIV/0!	6	TRUE	#N/A	#N/A	#N/A
94		#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	book	FALSE	#N/A	#N/A	#N/A	#N/A
95		#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	79	text	14	#N/A	#N/A	#N/A
96
97		vstacks:	{2,1,3,0,2}	intended result:
98		#DIV/0!	6	TRUE	#DIV/0!	6	TRUE	#DIV/0!	6	TRUE	#DIV/0!	6	TRUE
99		book	FALSE	#N/A	book	FALSE	#N/A	book	FALSE	#N/A	book	FALSE	#N/A
100		79	text	14	79	text	14	79	text	14	79	text	14
101		#DIV/0!	6	TRUE				#DIV/0!	6	TRUE	#DIV/0!	6	TRUE
102		book	FALSE	#N/A				book	FALSE	#N/A	book	FALSE	#N/A
103		79	text	14				79	text	14	79	text	14
104								#DIV/0!	6	TRUE
105								book	FALSE	#N/A
106								79	text	14
107
108		vstacks:	{2,1,3,-4,2}	intended result:
109		#DIV/0!	6	TRUE	#DIV/0!	6	TRUE	#DIV/0!	6	TRUE				#DIV/0!	6	TRUE
110		book	FALSE	#N/A	book	FALSE	#N/A	book	FALSE	#N/A				book	FALSE	#N/A
111		79	text	14	79	text	14	79	text	14				79	text	14
112		#DIV/0!	6	TRUE				#DIV/0!	6	TRUE				#DIV/0!	6	TRUE
113		book	FALSE	#N/A				book	FALSE	#N/A				book	FALSE	#N/A
114		79	text	14				79	text	14				79	text	14
115								#DIV/0!	6	TRUE
116								book	FALSE	#N/A
117								79	text	14
118
119
120
121
122		vstacks:	{0,2,1,3,0,2,0,0}	intended result:
123		#DIV/0!	6	TRUE	#DIV/0!	6	TRUE	#DIV/0!	6	TRUE	#DIV/0!	6	TRUE
124		book	FALSE	#N/A	book	FALSE	#N/A	book	FALSE	#N/A	book	FALSE	#N/A
125		79	text	14	79	text	14	79	text	14	79	text	14
126		#DIV/0!	6	TRUE				#DIV/0!	6	TRUE	#DIV/0!	6	TRUE
127		book	FALSE	#N/A				book	FALSE	#N/A	book	FALSE	#N/A
128		79	text	14				79	text	14	79	text	14
129								#DIV/0!	6	TRUE
130								book	FALSE	#N/A
131								79	text	14
132
133		vstacks:	{-2,1,3,0,2}	intended result:
134					#DIV/0!	6	TRUE	#DIV/0!	6	TRUE	#DIV/0!	6	TRUE
135					book	FALSE	#N/A	book	FALSE	#N/A	book	FALSE	#N/A
136					79	text	14	79	text	14	79	text	14
137								#DIV/0!	6	TRUE	#DIV/0!	6	TRUE
138								book	FALSE	#N/A	book	FALSE	#N/A
139								79	text	14	79	text	14
140								#DIV/0!	6	TRUE
141								book	FALSE	#N/A
142								79	text	14
143
sheet1

Cell Formulas
Range	Formula
B70:Q81	B70	=LET(r,{#DIV/0!,6,TRUE;"book",FALSE,#N/A;79,"text",14},b,{2,1,3,4,2},c,ROWS(r),d,COLUMNS(r),e,COLUMNS(b),v,LAMBDA(n,INDEX(r,MOD(SEQUENCE(n*c,1,0),c)+1,SEQUENCE(1,d))),REDUCE("",SEQUENCE(1,e),LAMBDA(x,i,HSTACK(x,v(INDEX(b,i))))))
B84:P95	B84	=LET(r,{#DIV/0!,6,TRUE;"book",FALSE,#N/A;79,"text",14},b,{2,1,3,4,2},c,ROWS(r),d,COLUMNS(r),e,COLUMNS(b),v,LAMBDA(n,INDEX(r,MOD(SEQUENCE(n*c,1,0),c)+1,SEQUENCE(1,d))),s,REDUCE("",SEQUENCE(1,e),LAMBDA(x,i,HSTACK(x,v(INDEX(b,i))))),DROP(s,,1))
Dynamic array formulas.

 

[jkpieterse]

Not sure. The #N/A values are where you haven't provided the "right" # of rows and/or columns for the stacking. To avoid them you'll have to find a way to include empty strings to fill those gaps somehow.

 

[Rnkhch]

Yes, I tried many different ways of inserting blanks and none worked

 

[RoryA]

You could replace the real #N/A errors in the original with something else, then IFNA your output to get blanks, then put the original #N/A errors back:

=LET(r,{#DIV/0!,6,TRUE;"book",FALSE,#N/A;79,"text",14},cleanr,IFNA(r,"~N/A~"),b,{2,1,3,4,2},c,ROWS(r),d,COLUMNS(r),e,COLUMNS(b),v,LAMBDA(n,INDEX(cleanr,MOD(SEQUENCE(n*c,1,0),c)+1,SEQUENCE(1,d))),out,IFNA(REDUCE("",SEQUENCE(1,e),LAMBDA(x,i,HSTACK(x,v(INDEX(b,i))))),""),IF(out="~N/A~",NA(),out))

 

[jkpieterse]

@RoryA that is devious!

 

[Rnkhch]

@RoryA Interesting, thanks!

 

[djclements]

The EXPAND function can be leveraged here to fill the extra rows with empty strings (""). The following variant should take care of your first 3 questions:

=LET(
   a,{#DIV/0!,6,TRUE;"book",FALSE,#N/A;79,"text",14},
   b,{0,2,1,3,-4,2,0},
   v,TOCOL(FILTER(b,b<>0,1)),
   r,ROWS(a),
   h,MAX(ABS(v))*r,
   w,COLUMNS(a),
   f,LAMBDA(n,EXPAND(IF(n<0,"",INDEX(a,MOD(SEQUENCE(n*r,1,0),r)+1,SEQUENCE(1,w))),h,w,"")),
   o,f(@v),
   IF(ROWS(v)=1,o,REDUCE(o,DROP(v,1),LAMBDA(x,i,HSTACK(x,f(i)))))
)

The FILTER function is used to remove any 0's from the number array, and TOCOL is used to ensure consistency with DROP(v,1), so that either a horizontal or vertical number array can be specified. To avoid the unwanted first column, the first element of the number array is processed first and is passed to the initial_value argument of REDUCE only if the number of elements is greater than 1.

I hope that helps. Cheers!

 

[Rnkhch]

@djclements Amazing, thanks! You are an absolute genius not relative

 

[djclements]

Regarding your fourth question, a binary tree method* can be used instead of REDUCE to significantly improve performance as the number of iterations increases. For example:

=LET(
   a,{#DIV/0!,6,TRUE;"book",FALSE,#N/A;79,"text",14},
   b,TOROW(IF(SEQUENCE(910),{0,2,1,3,-4,2,0,-1})),
   v,TOCOL(FILTER(b,b<>0,1)),
   r,ROWS(a),
   h,MAX(ABS(v))*r,
   w,COLUMNS(a),
   λ,LAMBDA(n,EXPAND(IF(n<0,"",INDEX(a,MOD(SEQUENCE(n*r,1,0),r)+1,SEQUENCE(1,w))),h,w,"")),
   f,LAMBDA(x,y,IF(ROWS(y)=1,λ(@y),HSTACK(x(x,TAKE(y,ROWS(y)/2)),x(x,DROP(y,ROWS(y)/2))))),
   f(f,v)
)

On my system, the above test output 5,460 stacked arrays (12 rows x 16,380 columns) in less than half a second, whereas REDUCE took 1 minute and 44 seconds.

Please note, overall efficiency will still be relative to the size of the input array. Cheers!

*Source: Recursive LAMBDA implementation of Excel's REDUCE function (member: lori_m)

 

[Rnkhch]

Holy cow this is unbelievable!! Thanks much! I'm gonna digest this as this method can improve the other functions I've made or I'm currently making, including the multi-hstack version of the same function of this post. I'll reach out if I get stuck

(Too bad the "Evaluate Formula" feature of Excel skips all custom formulas and makes it harder to follow what each step does)

You are an absolute super genius not relative