REDUCE function not working when feeding result of FILTER function. Why?

[felixstraube]

Hi there.
I was scratching my head because of a REDUCE function that wouldn't work as expected. And I still can't figure out why. Can you help me?
It doesn't make much sense what the REDUCE function does, because it is not complete, but you'll see my problem.

Book1
	A	B	C	D
1	Text01			#VALUE!
2	Text02		Text01
3			Text02
4	Text03		0
5			Text03
6	Text04		0
7	Text05		Text04
8			Text05
9	Text06		0
10	Text07		Text06
11	Text08		Text07
12			Text08
13	Text09		0
14	Text10		Text09
15			Text10
Sheet1

Cell Formulas
Range	Formula
C1:C15	C1	=LET(t, A1:A100, maxrow, MAX(ROW(t)*(t<>"")), d, TAKE(t, maxrow), s, REDUCE("", d, LAMBDA(ini,x, VSTACK(ini,x))), s )
D1	D1	=LET(t, A1:A100, maxrow, MAX(ROW(t)*(t<>"")), d, FILTER(t, ROW(t)<=maxrow), s, REDUCE("", d, LAMBDA(ini,x, VSTACK(ini,x))), s )
Dynamic array formulas.

In column A, I will paste some text. The number of rows may change so I need to get the last row with data, and take or filter only those rows from start to the last with text (d is the filtered data). Then I feed d to the REDUCE function.
In C1, it works fine when I filter the data with TAKE. But the formula in D1, when I use FILTER, REDUCE will throws an #VALUE! error.

Here we can see that the content of d should be the same in both formulas:

Book1
	A	B	C	D
1	Text01		Text01	Text01
2	Text02		Text02	Text02
3			0	0
4	Text03		Text03	Text03
5			0	0
6	Text04		Text04	Text04
7	Text05		Text05	Text05
8			0	0
9	Text06		Text06	Text06
10	Text07		Text07	Text07
11	Text08		Text08	Text08
12			0	0
13	Text09		Text09	Text09
14	Text10		Text10	Text10
Sheet1

Cell Formulas
Range	Formula
C1:C14	C1	=LET(t, A1:A100, maxrow, MAX(ROW(t)*(t<>"")), d, TAKE(t, maxrow), s, REDUCE("", d, LAMBDA(ini,x, VSTACK(ini,x))), d )
D1:D14	D1	=LET(t, A1:A100, maxrow, MAX(ROW(t)*(t<>"")), d, FILTER(t, ROW(t)<=maxrow), s, REDUCE("", d, LAMBDA(ini,x, VSTACK(ini,x))), d )
Dynamic array formulas.

But somehow the result of FILTER is different from the one of the TAKE function and REDUCE won't work.
Can you help me understand what is happening here?

 

[RoryA]

I don't really know why, but this seems to work:

=LET(t, A1:A100,
maxrow, MAX(ROW(t)*(t<>"")),
d, FILTER(t, ROW(t)<=maxrow),
s, REDUCE("", d, LAMBDA(ini,x, VSTACK(ini,T(x)))),
s
)

 

[felixstraube]

Nice. Thank you. The question remains why this behavior.
And I add another weird behavior...

If I apply T() to the result of FILTER it works fine, if I do it to the result of TAKE, it returns only the first item:

Book1
	A	B	C	D
1	Text01
2	Text02		Text01	Text01
3				Text02
4	Text03
5				Text03
6	Text04
7	Text05			Text04
8				Text05
9	Text06
10	Text07			Text06
11	Text08			Text07
12				Text08
13	Text09
14	Text10			Text09
15				Text10
Sheet1

Cell Formulas
Range	Formula
C1:C2	C1	=LET(t, A1:A100, maxrow, MAX(ROW(t)*(t<>"")), d, T(TAKE(t, maxrow)), s, REDUCE("", d, LAMBDA(ini,x, VSTACK(ini,x))), s )
D1:D15	D1	=LET(t, A1:A100, maxrow, MAX(ROW(t)*(t<>"")), d, T(FILTER(t, ROW(t)<=maxrow)), s, REDUCE("", d, LAMBDA(ini,x, VSTACK(ini,x))), s )
Dynamic array formulas.

What am I missing here? I thought both functions return dynamic arrays. Why this different behavior?

 

[KRice]

Rory's solution got me curious. This also seems to work:

Book1
	I	J
1
2	Text01	Text01
3	Text02	Text02
4
5	Text03	Text03
6
7	Text04	Text04
8	Text05	Text05
9
10	Text06	Text06
11	Text07	Text07
12	Text08	Text08
13
14	Text09	Text09
15	Text10	Text10
Sheet1

Cell Formulas
Range	Formula
I1:I15	I1	=LET(t, A1:A100, maxrow, MAX(ROW(t)*(t<>"")), d, TAKE(t, maxrow)&"", s, REDUCE("", d, LAMBDA(ini,x, VSTACK(ini,x))), s )
J1:J15	J1	=LET(t, A1:A100, maxrow, MAX(ROW(t)*(t<>"")), d, FILTER(t, ROW(t)<=maxrow)&"", s, REDUCE("", d, LAMBDA(ini,x, VSTACK(ini,x))), s )
Dynamic array formulas.

These coerce elements in d to text by appending "".

 

[RoryA]

TAKE returns one range/array (depending on what is passed to it) as it can only return one contiguous block. FILTER will return only an array. I would only use T() in the REDUCE, not on the overall resultant array.

 

[felixstraube]

Thank you both for your time. Interesting what you found out.
It seems that the objects returned as an array by both functions are not exactly the same and are forced to a certain type (sting probably right?) when concatenated with "".
I think this is something similar to when we used SUBTOTAL with OFFSET to slice and array and apply the operation to one row at a time before excel 365. Or when you use something like this: N(IF({1},Elements)) to make INDEX work as expected in array formulas (like described here: Can Excel's INDEX function return array?)
I still don't know why these things work I just know they work. I would to love to learn why.

 

[Fluff]

I can only think it's something to do with the fact that in your example TAKE is passing a range, whereas FILTER is passing an array.
Interestingly the formula in E5 works, but not the one in E9

Fluff.xlsm
	A	B	C	D	E
1	Text01			#VALUE!
2	Text02		Text01
3			Text02
4	Text03		0
5			Text03		0
6	Text04		0		0
7	Text05		Text04		0
8			Text05
9	Text06		0		#VALUE!
10	Text07		Text06
11	Text08		Text07
12			Text08
13	Text09		0
14	Text10		Text09
15			Text10
16
Sheet5

Cell Formulas
Range	Formula
C1:C15	C1	=LET(t, A1:A100,maxrow, MAX(ROW(t)*(t<>"")),d, TAKE(t, maxrow),s, REDUCE("", d, LAMBDA(ini,x, VSTACK(ini,x))),s)
D1	D1	=LET(t, A1:A100,maxrow, MAX(ROW(t)*(t<>"")),d, FILTER(t, ROW(t)<=maxrow),s, REDUCE("", d, LAMBDA(ini,x, VSTACK(ini,x))),s)
E5:E7	E5	=VSTACK(A5,A8,FILTER(A11:A14,A11:A14=""))
E9	E9	=VSTACK(A5,A8,INDEX(FILTER(A11:A14,A11:A14=""),1))
Dynamic array formulas.

 

[KRice]

Interesting...apparently the blanks are the source of the problem? If the blank is converted to blank text by appending "" (as in E9) the error is avoided. Similarly, if the initial range is pre-processed (see col F) to convert "blanks" to "", the INDEX/FILTER component in VSTACK doesn't generate an error.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Text01			#VALUE!		Text01				FALSE
2	Text02		Text01			Text02				FALSE
3			Text02							TRUE
4	Text03		0			Text03				FALSE
5			Text03		0					TRUE
6	Text04		0		0	Text04				FALSE
7	Text05		Text04		0	Text05				FALSE
8			Text05							TRUE
9	Text06		0		0	Text06				FALSE
10	Text07		Text06		0	Text07				FALSE
11	Text08		Text07			Text08				FALSE
12			Text08							TRUE
13	Text09		0			Text09				FALSE
14	Text10		Text09			Text10				FALSE
15			Text10							TRUE
Sheet2

Cell Formulas
Range	Formula
C1:C15	C1	=LET(t, A1:A100,maxrow, MAX(ROW(t)*(t<>"")),d, TAKE(t, maxrow),s, REDUCE("", d, LAMBDA(ini,x, VSTACK(ini,x))),s)
D1	D1	=LET(t, A1:A100,maxrow, MAX(ROW(t)*(t<>"")),d, FILTER(t, ROW(t)<=maxrow),s, REDUCE("", d, LAMBDA(ini,x, VSTACK(ini,x))),s)
E5:E7	E5	=VSTACK(A5,A8,FILTER(A11:A14,A11:A14=""))
H5:H7	H5	=VSTACK(F5,F8,FILTER(F11:F14,F11:F14=""))
E9:E11	E9	=VSTACK(A5,A8,INDEX(FILTER(A11:A14,A11:A14="")&"",1))
H9:H11	H9	=VSTACK(F5,F8,INDEX(FILTER(F11:F14,F11:F14=""),1))
F1:F14	F1	=IF(ISBLANK(A1),"",A1)
J1:J15	J1	=ISBLANK(A1)
Dynamic array formulas.

 

[felixstraube]

Interesting what you both found out.
Would it be correct to assume that the FILTER function has a bug?
If so should this be reported to Microsoft?

 

[Fluff]

I wouldn't call it a bug (especially not with filter), just that different functions handle empty cells differently.