Is it possible to use FILTER with BYROW to get a spill? If not, how else can this be done?

[Rnkhch]

Hello,

Suppose I have the range in the attached XL2BB, and I FILTER each row for numbers greater than 50. Can use BYROW to get the indicated result in a single spill? If not, is there another way?

=BYROW(B3:G11,LAMBDA(a,FILTER(a,a>50)))
this gives the "nested arrays not supported" error

Blank power workbook1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2									desired output
3		17	54	49	69	46	17		54	69
4		65	98	50	42	52	100		65	98	52	100
5		69	44	77	18	49	94		69	77	94
6		68	89	22	40	47	26		68	89
7		44	33	24	27	77	80		77	80
8		53	14	82	91	53	88		53	82	91	53	88
9		83	16	96	95	97	63		83	96	95	97	63
10		22	83	60	86	30	96		83	60	86	96
11		98	33	89	95	51	57		98	89	95	51	57
12
Sheet3

Thanks for any input!

 

[Rnkhch]

Hi Peter,

Your second formula (below) is amazing, and I made a lot of progress on it:

=IFERROR(TEXTSPLIT(SUBSTITUTE(TEXTJOIN(",",1,SCAN("",B3:G11,LAMBDA(a,b,IF(COLUMN(b)=COLUMN(B3),"|","")&IF(b>50,b,"")))),"|,","|"),",","|",1)+0,"")

But, it appears that it only works when the SCAN array (first bolded section in the above formula) is a selected range, and as soon as I substitute it with an array (as in the attached XL2BB and below), the formula crashes. I believe the issue comes from the part of the formula that deals with columns (second bolded section).

By any chance, could you do a magic so that the modified formula also works?

=IFERROR(TEXTSPLIT(SUBSTITUTE(TEXTJOIN(",",1,SCAN("",IF(I3#,B3:G11,""),LAMBDA(a,b,IF(COLUMN(b)=COLUMN(B3),"|","")&IF(b>50,b,"")))),"|,","|"),",","|",1)+0,"")

newtemp 2022 12 23.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2
3		17	54	49	69	46	17		1	1	1	1	1	1
4		65	98	50	42	52	100		1	1	1	1	1	1
5		69	44	77	18	49	94		1	1	1	1	1	1
6		68	89	22	40	book	26		1	1	1	1	0	1
7		44	33	24	27	77	80		1	1	1	1	1	1
8		53	14	82	91	53	88		1	1	1	1	1	1
9		83	16	c	95	97	63		1	1	0	1	1	1
10		22	83	60	86	30	96		1	1	1	1	1	1
11		98	33	89	95	51	57		1	1	1	1	1	1
12
13		Text
14		54	69
15		65	98	52	100
16		69	77	94
17		68	89	book
18		77	80
19		53	82	91	53	88
20		83	c	95	97	63
21		83	60	86	96
22		98	89	95	51	57
23
24		Numerical
25
26
27
28
29
30
31
32
33
34
Sheet5

Cell Formulas
Range	Formula
I3:N11	I3	=IF(ISNUMBER(B3:G11),1,0)
B14:F22	B14	=TEXTSPLIT(SUBSTITUTE(TEXTJOIN(",",1,SCAN("",B3:G11,LAMBDA(a,b,IF(COLUMN(b)=COLUMN(B3),"|","")&IF(b>50,b,"")))),"|,","|"),",","|",1,,"")
B25	B25	=IFERROR(TEXTSPLIT(SUBSTITUTE(TEXTJOIN(",",1,SCAN("",IF(I3#,B3:G11,""),LAMBDA(a,b,IF(COLUMN(b)=COLUMN(B3),"|","")&IF(b>50,b,"")))),"|,","|"),",","|",1)+0,"")
Dynamic array formulas.

 

[Peter_SSs]

I'm not sure what your ultimate goal is, but I'm wondering why have the separate I3# just to determine if B3:G11 is a number or not? Why not just add that check into the existing formula?

=IFERROR(TEXTSPLIT(SUBSTITUTE(TEXTJOIN(",",1,SCAN("",B3:G11,LAMBDA(a,b,IF(COLUMN(b)=COLUMN(B3),"|","")&IF(AND(ISNUMBER(b),b>50),b,"")))),"|,","|"),",","|",1)+0,"")

 

[Rnkhch]

Thanks Peter. So, in the final LAMBDA, I'll be using my ISXTYPE function for high-resolution data-type checking/picking, so the I3# will be generated like in the examples below:

IF(ISXTYPE(B3:G11,1),1,0) (where 1 means number)
IF(ISXTYPE(B3:G11,{1,2}),1,0) (both numbers and text)
etc.

This way, I can ask the formula to only pick any one or more of the data types (in my most recent version of ISXTYPE which I haven't posted in the LAMBDA forum yet, you could pick any single or combination of data types that you want such as {1,4} or {2,16} or {1,32,41}, etc.)

So my goal is to leave the part of your formula that contains b>50 for the purpose of checking "match_criteria", (which may or may not be entered).

That's the reason that I wanted to be able to include an array in your formula at the first bolded location in my post #11.

By the way, here is how the intended LAMBDA for this kind of filtering will look like (not finalized yet; I may add other parameters):

FUNCTION(array,types,[filter_by],[match_criteria],[if_empty])
where filter_by will get 0 (or empty) for filtering by rows and 1 by columns

Let me know if you need more explanation.

Thanks

 

[Peter_SSs]

Over my head I think.

 

[Xlambda]

I would going to try an Ai chat to explain a formula, but it is currently down. . Not sure if they work for excel formulas. You should check out @Xlambda's ABYROW formula.

Hi guys, sorry to bother, I am here because I just saw the notification that I was mentioned by @jaeiow.
All you need is a general use (like BYROW) lambda helper function that can spill results by row.
It is called RBYROW, Row Byrow same arguments as BYROW. Check this link: AUNIQUE

=LAMBDA(ar, fn,
    LET(
        a, IF(ar = "", "", ar),
        c, --(COLUMNS(a) = 1),
        r, REDUCE(0, SEQUENCE(ROWS(a)), LAMBDA(v, i, VSTACK(v, fn(INDEX(a, i, c))))),
        DROP(IFNA(r, ""), 1)
    )
)

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
1																											Task: sort unique filter values >15, by row
2																				sample
3										=RBYROW(B4:G12,LAMBDA(x,FILTER(x,x>50,"")))										=RANDARRAY(9,6,10,30,1)							=RBYROW(T4#,LAMBDA(x,SORT(UNIQUE(FILTER(x,x>15),1),,,1)))
4		17	54	49	69	46	17			54	69									26	28	15	27	29	25		25	26	27	28	29
5		65	98	50	42	52	100			65	98	52	100							22	12	18	15	16	10		16	18	22
6		69	44	77	18	49	94			69	77	94								23	24	26	25	14	23		23	24	25	26
7		68	89	22	40	47	26			68	89									21	14	19	27	21	21		19	21	27
8		44	33	24	27	77	80			77	80									24	28	17	14	27	27		17	24	27	28
9		53	14	82	91	53	88			53	82	91	53	88						19	26	18	14	29	24		18	19	24	26	29
10		83	16	96	95	97	63			83	96	95	97	63						28	15	14	22	16	19		16	19	22	28
11		22	83	60	86	30	96			83	60	86	96							24	24	24	13	10	17		17	24
12		98	33	89	95	51	57			98	89	95	51	57						25	14	28	14	19	26		19	25	26	28
13
14		if some row has no results																		Now, let's say that for a very repetitive task we alteady have a lambda Sort Unique Grater Than 15 SUGT15
15										=RBYROW(B16:G25,LAMBDA(x,FILTER(x,x>50,"")))										SUGT15(x)=LAMBDA(x,SORT(UNIQUE(FILTER(x,x>15),1),,,1))
16		17	54	49	69	46	17			54	69
17		65	98	50	42	52	100			65	98	52	100														We just call:
18		69	44	77	18	49	94			69	77	94															=RBYROW(T4#,SUGT15)
19		68	89	22	40	47	26			68	89																25	26	27	28	29
20		44	33	24	27	77	80			77	80																16	18	22
21		34	49	49	29	47	48																				23	24	25	26
22		53	14	82	91	53	88			53	82	91	53	88													19	21	27
23		83	16	96	95	97	63			83	96	95	97	63													17	24	27	28
24		22	83	60	86	30	96			83	60	86	96														18	19	24	26	29
25		98	33	89	95	51	57			98	89	95	51	57													16	19	22	28
26																											17	24
27																											19	25	26	28
28
Sheet2

Cell Formulas
Range	Formula
J3,AA18,J15,AA3,T3	J3	=FORMULATEXT(J4)
J4:N12	J4	=RBYROW(B4:G12,LAMBDA(x,FILTER(x,x>50,"")))
T4:Y12	T4	=RANDARRAY(9,6,10,30,1)
AA4:AE12	AA4	=RBYROW(T4#,LAMBDA(x,SORT(UNIQUE(FILTER(x,x>15),1),,,1)))
J16:N25	J16	=RBYROW(B16:G25,LAMBDA(x,FILTER(x,x>50,"")))
AA19:AE27	AA19	=RBYROW(T4#,SUGT15)
Dynamic array formulas.

 

[Rnkhch]

Thank you so much @Xlambda! I actually meant to ask your opinion. This is very helpful!

I'll work with RBYROW and see if I can make it automatically remove the rows that come out as empty.

Also I was searching for RBYCOL to see if you have posted it, but the search on the LAMBDA forum returns no results. I assumed you probably created RBYCOL also? If so, could you please share the code? Thanks much, and Happy Holidays!

 

[jaeiow]

CBYCOL, column bycol (I know, it gets rough with the names). It is posted here: AUNIQUE
But if you mean Row bycol, I'm not sure if something exists or what the use case would be.

 

[Rnkhch]

I know, it gets rough with the names

Yes, lol, I totally missed that

Found it, thanks!

 

[Xlambda]

Thank you so much @Xlambda! I actually meant to ask your opinion. This is very helpful!

I'll work with RBYROW and see if I can make it automatically remove the rows that come out as empty.

Also I was searching for RBYCOL to see if you have posted it, but the search on the LAMBDA forum returns no results. I assumed you probably created RBYCOL also? If so, could you please share the code? Thanks much, and Happy Holidays!

You are very welcome!
CBYCOL Column BYCOL

=LAMBDA(ar, fn,
    LET(
        a, IF(ar = "", "", ar),
        w, --(ROWS(a) = 1),
        r, REDUCE(0, SEQUENCE(COLUMNS(a)), LAMBDA(v, i, HSTACK(v, fn(INDEX(a, w, i))))),
        DROP(IFNA(r, ""), , 1)
    )
)

And a formula to exclude empty rows(assuming entire row is empty when first value/row is empty), Also is easy to change the function if you need other conditions like >45.
=LET(a,B16:G25,fn,LAMBDA(x,FILTER(x,x>50,"")),r,RBYROW(a,fn),FILTER(r,TAKE(r,,1)<>""))

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
1																											Task: sort unique filter values >15, by row
2																				sample
3										=RBYROW(B4:G12,LAMBDA(x,FILTER(x,x>50,"")))										=RANDARRAY(9,6,10,30,1)							=RBYROW(T4#,LAMBDA(x,SORT(UNIQUE(FILTER(x,x>15),1),,,1)))
4		17	54	49	69	46	17			54	69									30	16	11	24	26	30		16	24	26	30
5		65	98	50	42	52	100			65	98	52	100							26	27	20	13	11	17		17	20	26	27
6		69	44	77	18	49	94			69	77	94								16	26	16	27	16	23		16	23	26	27
7		68	89	22	40	47	26			68	89									19	10	13	15	24	20		19	20	24
8		44	33	24	27	77	80			77	80									21	27	13	16	30	18		16	18	21	27	30
9		53	14	82	91	53	88			53	82	91	53	88						21	13	25	27	10	16		16	21	25	27
10		83	16	96	95	97	63			83	96	95	97	63						18	22	11	14	26	11		18	22	26
11		22	83	60	86	30	96			83	60	86	96							29	14	13	12	14	10		29
12		98	33	89	95	51	57			98	89	95	51	57						14	15	25	14	18	22		18	22	25
13
14		if some row has no results																		Now, let's say that for a very repetitive task we alteady have a lambda Sort Unique Grater Than 15 SUGT15
15										=RBYROW(B16:G25,LAMBDA(x,FILTER(x,x>50,"")))										SUGT15(x)=LAMBDA(x,SORT(UNIQUE(FILTER(x,x>15),1),,,1))
16		17	54	49	69	46	17			54	69
17		65	98	50	42	52	100			65	98	52	100														We just call:
18		69	44	77	18	49	94			69	77	94															=RBYROW(T4#,SUGT15)
19		68	89	22	40	47	26			68	89																16	24	26	30
20		44	33	24	27	77	80			77	80																17	20	26	27
21		34	49	49	29	47	48																				16	23	26	27
22		53	14	82	91	53	88			53	82	91	53	88													19	20	24
23		83	16	96	95	97	63			83	96	95	97	63													16	18	21	27	30
24		22	83	60	86	30	96			83	60	86	96														16	21	25	27
25		98	33	89	95	51	57			98	89	95	51	57													18	22	26
26																											29
27										extracting empty rows																	18	22	25
28										=LET(a,B16:G25,fn,LAMBDA(x,FILTER(x,x>50,"")),r,RBYROW(a,fn),FILTER(r,TAKE(r,,1)<>""))
29										54	69
30										65	98	52	100
31										69	77	94
32										68	89
33										77	80
34										53	82	91	53	88
35										83	96	95	97	63
36										83	60	86	96
37										98	89	95	51	57
38
Sheet2

Cell Formulas
Range	Formula
J3,J28,AA18,J15,AA3,T3	J3	=FORMULATEXT(J4)
J4:N12	J4	=RBYROW(B4:G12,LAMBDA(x,FILTER(x,x>50,"")))
T4:Y12	T4	=RANDARRAY(9,6,10,30,1)
AA4:AE12	AA4	=RBYROW(T4#,LAMBDA(x,SORT(UNIQUE(FILTER(x,x>15),1),,,1)))
J16:N25	J16	=RBYROW(B16:G25,LAMBDA(x,FILTER(x,x>50,"")))
AA19:AE27	AA19	=RBYROW(T4#,SUGT15)
J29:N37	J29	=LET(a,B16:G25,fn,LAMBDA(x,FILTER(x,x>50,"")),r,RBYROW(a,fn),FILTER(r,TAKE(r,,1)<>""))
Dynamic array formulas.

 

[Rnkhch]

Fabulous Thanks much!