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

Rnkhch

Well-known Member
Joined
Apr 28, 2018
Messages
563
Office Version
  1. 365
Platform
  1. Windows
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.)

Excel Formula:
=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
ABCDEFGHIJKLMNOPQR
68
69vstacks:{2,1,3,4,2}
70 #DIV/0!6TRUE#DIV/0!6TRUE#DIV/0!6TRUE#DIV/0!6TRUE#DIV/0!6TRUE
71#N/AbookFALSE#N/AbookFALSE#N/AbookFALSE#N/AbookFALSE#N/AbookFALSE#N/A
72#N/A79text1479text1479text1479text1479text14
73#N/A#DIV/0!6TRUE#N/A#N/A#N/A#DIV/0!6TRUE#DIV/0!6TRUE#DIV/0!6TRUE
74#N/AbookFALSE#N/A#N/A#N/A#N/AbookFALSE#N/AbookFALSE#N/AbookFALSE#N/A
75#N/A79text14#N/A#N/A#N/A79text1479text1479text14
76#N/A#N/A#N/A#N/A#N/A#N/A#N/A#DIV/0!6TRUE#DIV/0!6TRUE#N/A#N/A#N/A
77#N/A#N/A#N/A#N/A#N/A#N/A#N/AbookFALSE#N/AbookFALSE#N/A#N/A#N/A#N/A
78#N/A#N/A#N/A#N/A#N/A#N/A#N/A79text1479text14#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!6TRUE#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/AbookFALSE#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/A79text14#N/A#N/A#N/A
82
83vstacks:{2,1,3,4,2}
84#DIV/0!6TRUE#DIV/0!6TRUE#DIV/0!6TRUE#DIV/0!6TRUE#DIV/0!6TRUE
85bookFALSE#N/AbookFALSE#N/AbookFALSE#N/AbookFALSE#N/AbookFALSE#N/A
8679text1479text1479text1479text1479text14
87#DIV/0!6TRUE#N/A#N/A#N/A#DIV/0!6TRUE#DIV/0!6TRUE#DIV/0!6TRUE
88bookFALSE#N/A#N/A#N/A#N/AbookFALSE#N/AbookFALSE#N/AbookFALSE#N/A
8979text14#N/A#N/A#N/A79text1479text1479text14
90#N/A#N/A#N/A#N/A#N/A#N/A#DIV/0!6TRUE#DIV/0!6TRUE#N/A#N/A#N/A
91#N/A#N/A#N/A#N/A#N/A#N/AbookFALSE#N/AbookFALSE#N/A#N/A#N/A#N/A
92#N/A#N/A#N/A#N/A#N/A#N/A79text1479text14#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!6TRUE#N/A#N/A#N/A
94#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/AbookFALSE#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/A79text14#N/A#N/A#N/A
96
97vstacks:{2,1,3,0,2}intended result:
98#DIV/0!6TRUE#DIV/0!6TRUE#DIV/0!6TRUE#DIV/0!6TRUE
99bookFALSE#N/AbookFALSE#N/AbookFALSE#N/AbookFALSE#N/A
10079text1479text1479text1479text14
101#DIV/0!6TRUE#DIV/0!6TRUE#DIV/0!6TRUE
102bookFALSE#N/AbookFALSE#N/AbookFALSE#N/A
10379text1479text1479text14
104#DIV/0!6TRUE
105bookFALSE#N/A
10679text14
107
108vstacks:{2,1,3,-4,2}intended result:
109#DIV/0!6TRUE#DIV/0!6TRUE#DIV/0!6TRUE#DIV/0!6TRUE
110bookFALSE#N/AbookFALSE#N/AbookFALSE#N/AbookFALSE#N/A
11179text1479text1479text1479text14
112#DIV/0!6TRUE#DIV/0!6TRUE#DIV/0!6TRUE
113bookFALSE#N/AbookFALSE#N/AbookFALSE#N/A
11479text1479text1479text14
115#DIV/0!6TRUE
116bookFALSE#N/A
11779text14
118
119
120
121
122vstacks:{0,2,1,3,0,2,0,0}intended result:
123#DIV/0!6TRUE#DIV/0!6TRUE#DIV/0!6TRUE#DIV/0!6TRUE
124bookFALSE#N/AbookFALSE#N/AbookFALSE#N/AbookFALSE#N/A
12579text1479text1479text1479text14
126#DIV/0!6TRUE#DIV/0!6TRUE#DIV/0!6TRUE
127bookFALSE#N/AbookFALSE#N/AbookFALSE#N/A
12879text1479text1479text14
129#DIV/0!6TRUE
130bookFALSE#N/A
13179text14
132
133vstacks:{-2,1,3,0,2}intended result:
134#DIV/0!6TRUE#DIV/0!6TRUE#DIV/0!6TRUE
135bookFALSE#N/AbookFALSE#N/AbookFALSE#N/A
13679text1479text1479text14
137#DIV/0!6TRUE#DIV/0!6TRUE
138bookFALSE#N/AbookFALSE#N/A
13979text1479text14
140#DIV/0!6TRUE
141bookFALSE#N/A
14279text14
143
sheet1
Cell Formulas
RangeFormula
B70:Q81B70=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:P95B84=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.
 
@djclements I managed to write the multi-hstack version of your function :cool::

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

It was such a fun🎈exercise, and I learned a lot. Thanks again for the amazing formula 🤗
 
Upvote 0

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
One minor change that I missed in the MOD function:

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

Forum statistics

Threads
1,221,418
Messages
6,159,791
Members
451,589
Latest member
Harold14

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