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!

 

[Peter_SSs]

There may well be better ways, but this is what I came up with, depending on whether the output needs to be numerical or not.

22 12 18.xlsm
	A	B	C	D	E	F	G
1
2
3		17	54	49	69	46	17
4		65	98	50	42	52	100
5		69	44	77	18	49	94
6		68	89	22	40	47	26
7		44	33	24	27	77	80
8		53	14	82	91	53	88
9		83	16	96	95	97	63
10		22	83	60	86	30	96
11		98	33	89	95	51	57
12
13		Text
14		54	69
15		65	98	52	100
16		69	77	94
17		68	89
18		77	80
19		53	82	91	53	88
20		83	96	95	97	63
21		83	60	86	96
22		98	89	95	51	57
23
24		Numerical
25		54	69
26		65	98	52	100
27		69	77	94
28		68	89
29		77	80
30		53	82	91	53	88
31		83	96	95	97	63
32		83	60	86	96
33		98	89	95	51	57
Rnkhch

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

 

[ISY]

Hi

=IFERROR(--TEXTSPLIT(TEXTAFTER(BYROW(B3:G11,LAMBDA(K,CONCAT("|"&TOCOL(IF(K>50,K,x),3)))),"|",SEQUENCE(,6)),"|"),"")

 

[Fluff]

Another option

=DROP(REDUCE("",SEQUENCE(ROWS(B3:G11)),LAMBDA(x,y,VSTACK(x,EXPAND(FILTER(INDEX(B3:G11,y,0),INDEX(B3:G11,y,0)>50),,6,"")))),1)

 

[jdellasala]

Another option

=DROP(REDUCE("",SEQUENCE(ROWS(B3:G11)),LAMBDA(x,y,VSTACK(x,EXPAND(FILTER(INDEX(B3:G11,y,0),INDEX(B3:G11,y,0)>50),,6,"")))),1)

Awesome formula! I wish Evaluate Formula worked on formulas like this in a CELL environment where you could see what each thing is actually doing!

 

[Rnkhch]

Awesome, thanks all!

I'm gonna start studying all your functions, and I'll make a LAMBDA

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[jaeiow]

Awesome formula! I wish Evaluate Formula worked on formulas like this in a CELL environment where you could see what each thing is actually doing!

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.

 

[Rnkhch]

Thank you jaeiow for the great suggestion!

I would going to try an Ai chat to explain a formula, but it is currently down.

Does this forum have a chat feature?

 

[jaeiow]

No, i meant chatgpt. let me see if it woke up today and can detail Fluffs formula.

This Excel formula appears to use a combination of functions to filter a range of cells and return only the rows that meet a certain condition.

The REDUCE function applies a function (specified by the LAMBDA function) to a sequence of items and returns a single result. The SEQUENCE function generates a sequence of numbers from 1 to the number of rows in the range B3:G11. These numbers are passed to the LAMBDA function as the variable y, which is used to reference the current row of the range B3:G11 using the INDEX function.

The FILTER function is then used to select only the rows where the value in the first column (referenced using INDEX(B3:G11, y, 0)) is greater than 50. The EXPAND function is used to convert the filtered rows into a vertical range, and the VSTACK function is used to stack this range on top of the current value of x (which is passed to the LAMBDA function as a variable).

Finally, the DROP function is used to remove the first row from the final result.

Overall, this formula appears to be filtering the range B3:G11 to select only the rows where the value in the first column is greater than 50, and then returning the resulting range without the first row.