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



## Rnkhch (Dec 17, 2022)

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 workbook1ABCDEFGHIJKLMN12desired output3175449694617546946598504252100659852100569447718499469779466889224047266889744332427778077808531482915388538291538898316969597638396959763102283608630968360869611983389955157988995515712Sheet3

Thanks for any input!  🤗


----------



## Peter_SSs (Dec 18, 2022)

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.xlsmABCDEFG123175449694617465985042521005694477184994668892240472674433242777808531482915388983169695976310228360863096119833899551571213Text 145469156598521001669779417688918778019538291538820839695976321836086962298899551572324Numerical25546926659852100276977942868892977803053829153883183969597633283608696339889955157RnkhchCell FormulasRangeFormulaB14:F22B14=TEXTSPLIT(SUBSTITUTE(TEXTJOIN(",",1,SCAN("",B3:G11,LAMBDA(a,b,IF(COLUMN(b)=COLUMN(B3),"|","")&IF(b>50,b,"")))),"|,","|"),",","|",1,,"")B25:F33B25=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 (Dec 18, 2022)

Hi

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


----------



## Fluff (Dec 18, 2022)

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 (Dec 18, 2022)

Fluff said:


> Another option
> 
> 
> 
> ...


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 (Dec 18, 2022)

Awesome, thanks all! 🤗 

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


----------



## Fluff (Dec 19, 2022)

Glad we could help & thanks for the feedback.


----------



## jaeiow (Dec 19, 2022)

jdellasala said:


> 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 (Dec 19, 2022)

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 (Dec 19, 2022)

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.
> 
> ...


----------



## Rnkhch (Dec 17, 2022)

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 workbook1ABCDEFGHIJKLMN12desired output3175449694617546946598504252100659852100569447718499469779466889224047266889744332427778077808531482915388538291538898316969597638396959763102283608630968360869611983389955157988995515712Sheet3

Thanks for any input!  🤗


----------



## Rnkhch (Dec 24, 2022)

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.xlsxABCDEFGHIJKLMNO123175449694617111111465985042521001111115694477184994111111668892240book261111017443324277780111111853148291538811111198316c95976311011110228360863096111111119833899551571111111213Text 1454691565985210016697794176889book1877801953829153882083c95976321836086962298899551572324Numerical25 262728293031323334Sheet5Cell FormulasRangeFormulaI3:N11I3=IF(ISNUMBER(B3:G11),1,0)B14:F22B14=TEXTSPLIT(SUBSTITUTE(TEXTJOIN(",",1,SCAN("",B3:G11,LAMBDA(a,b,IF(COLUMN(b)=COLUMN(B3),"|","")&IF(b>50,b,"")))),"|,","|"),",","|",1,,"")B25B25=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 (Dec 25, 2022)

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 (Dec 25, 2022)

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 (Dec 25, 2022)

Over my head I think.


----------



## Xlambda (Dec 25, 2022)

jaeiow said:


> 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)
    )
)
```
Book1ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAK1Task: sort unique filter values >15, by row2sample3=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)))417544969461754692628152729252526272829565985042521006598521002212181516101618226694477184994697794232426251423232425267688922404726688921141927212119212784433242777807780242817142727172427289531482915388538291538819261814292418192426291083169695976383969597632815142216191619222811228360863096836086962424241310171724129833899551579889955157251428141926192526281314if some row has no resultsNow, let's say that for a very repetitive task we alteady have a lambda Sort Unique Grater Than 15 SUGT1515=RBYROW(B16:G25,LAMBDA(x,FILTER(x,x>50,"")))SUGT15(x)=LAMBDA(x,SORT(UNIQUE(FILTER(x,x>15),1),,,1))161754496946175469176598504252100659852100We just call:18694477184994697794=RBYROW(T4#,SUGT15)19688922404726688925262728292044332427778077801618222134494929474823242526225314829153885382915388192127238316969597638396959763172427282422836086309683608696181924262925983389955157988995515716192228261724271925262828Sheet2Cell FormulasRangeFormulaJ3,AA18,J15,AA3,T3J3=FORMULATEXT(J4)J4:N12J4=RBYROW(B4:G12,LAMBDA(x,FILTER(x,x>50,"")))T4:Y12T4=RANDARRAY(9,6,10,30,1)AA4:AE12AA4=RBYROW(T4#,LAMBDA(x,SORT(UNIQUE(FILTER(x,x>15),1),,,1)))J16:N25J16=RBYROW(B16:G25,LAMBDA(x,FILTER(x,x>50,"")))AA19:AE27AA19=RBYROW(T4#,SUGT15)Dynamic array formulas.


----------



## Rnkhch (Dec 25, 2022)

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 (Dec 25, 2022)

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 (Dec 25, 2022)

> I know, it gets rough with the names


Yes, lol, I totally missed that 😅 

Found it, thanks! 🤗


----------



## Xlambda (Dec 25, 2022)

Rnkhch said:


> 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)<>""))*
Book2ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAK1Task: sort unique filter values >15, by row2sample3=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)))417544969461754693016112426301624263056598504252100659852100262720131117172026276694477184994697794162616271623162326277688922404726688919101315242019202484433242777807780212713163018161821273095314829153885382915388211325271016162125271083169695976383969597631822111426111822261122836086309683608696291413121410291298338995515798899551571415251418221822251314if some row has no resultsNow, let's say that for a very repetitive task we alteady have a lambda Sort Unique Grater Than 15 SUGT1515=RBYROW(B16:G25,LAMBDA(x,FILTER(x,x>50,"")))SUGT15(x)=LAMBDA(x,SORT(UNIQUE(FILTER(x,x>15),1),,,1))161754496946175469176598504252100659852100We just call:18694477184994697794=RBYROW(T4#,SUGT15)196889224047266889162426302044332427778077801720262721344949294748162326272253148291538853829153881920242383169695976383969597631618212730242283608630968360869616212527259833899551579889955157182226262927extracting empty rows18222528=LET(a,B16:G25,fn,LAMBDA(x,FILTER(x,x>50,"")),r,RBYROW(a,fn),FILTER(r,TAKE(r,,1)<>""))2954693065985210031697794326889337780345382915388358396959763368360869637988995515738Sheet2Cell FormulasRangeFormulaJ3,J28,AA18,J15,AA3,T3J3=FORMULATEXT(J4)J4:N12J4=RBYROW(B4:G12,LAMBDA(x,FILTER(x,x>50,"")))T4:Y12T4=RANDARRAY(9,6,10,30,1)AA4:AE12AA4=RBYROW(T4#,LAMBDA(x,SORT(UNIQUE(FILTER(x,x>15),1),,,1)))J16:N25J16=RBYROW(B16:G25,LAMBDA(x,FILTER(x,x>50,"")))AA19:AE27AA19=RBYROW(T4#,SUGT15)J29:N37J29=LET(a,B16:G25,fn,LAMBDA(x,FILTER(x,x>50,"")),r,RBYROW(a,fn),FILTER(r,TAKE(r,,1)<>""))Dynamic array formulas.


----------



## Rnkhch (Dec 25, 2022)

Fabulous 🍻 Thanks much! 🤗


----------



## Rnkhch (Dec 17, 2022)

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 workbook1ABCDEFGHIJKLMN12desired output3175449694617546946598504252100659852100569447718499469779466889224047266889744332427778077808531482915388538291538898316969597638396959763102283608630968360869611983389955157988995515712Sheet3

Thanks for any input!  🤗


----------



## Xlambda (Dec 25, 2022)

Anytime !! ✌️😉


----------

