Forum Discussion

NothingHeavy's avatar
NothingHeavy
Copper Contributor
Aug 23, 2023
Solved

FILTER a Table using Spilled Dynamic Array as part of the "Include" Criteria

1. I have my first sheet (PO Lines) where the user will select a Top Level Part Number from a dropdown list.     2. My second sheet (Part Numbers) contains all the Top Level Part Numbers and...
excel
Formulas and Functions
  • mtarler's avatar
    mtarler
    Aug 24, 2023

    I think you are looking for 2-D Lambda function here. Maybe something like (note it is untested and just thrown together to give you an idea of how to make it work):

     

     

    =IFERROR(DROP(REDUCE("",L3#, LAMBDA(p,q, 
   HSTACK(p, VSTACK(q, FILTER(ComponentPNs[Component PNs], 
                              ComponentPNs[Top Level PN]=q,"")
          ))
   )),,1),"")

     

     

    if you attach a that sample or give a link to it I'd be happy to test it and make sure it works too 🙂

    BTW this also spits out the 'header' from L3# but if this formula will go in L4 then something more like this:

     

    =IFERROR(DROP(REDUCE("",L3#, LAMBDA(p,q, 
   HSTACK(p, FILTER(ComponentPNs[Component PNs], 
                    ComponentPNs[Top Level PN]=q,"")
          )
   )),,1),"")

    note: I edited it to add the IFERROR wrap to remove the error values make it look nicer 🙂

     

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Aug 23, 2023

NothingHeavy 

=VSTACK(TRANSPOSE(SORT(INDIRECT("A2:A"&COUNTA(A:A)))),IFERROR(DROP(REDUCE("",SEQUENCE(1,COUNT(TRANSPOSE(SORT(INDIRECT("A2:A"&COUNTA(A:A)))))),LAMBDA(x,y,HSTACK(x,FILTER(I2:I24,G2:G24=INDEX(TRANSPOSE(SORT(INDIRECT("A2:A"&COUNTA(A:A)))),,y))))),,1),""))

 

This formula returns the intended result in similar sample data.

  • NothingHeavy's avatar
    NothingHeavy
    Copper Contributor
    Aug 24, 2023
    I tried this method as well, formatting my data in the same way as in your test sheet, and modifying the formula for my worksheet but only returned the L3# cell values and another row of empty or #N/A cells.
    • mtarler's avatar
      mtarler
      Silver Contributor
      Aug 24, 2023

      I think you are looking for 2-D Lambda function here. Maybe something like (note it is untested and just thrown together to give you an idea of how to make it work):

       

       

      =IFERROR(DROP(REDUCE("",L3#, LAMBDA(p,q, 
   HSTACK(p, VSTACK(q, FILTER(ComponentPNs[Component PNs], 
                              ComponentPNs[Top Level PN]=q,"")
          ))
   )),,1),"")

       

       

      if you attach a that sample or give a link to it I'd be happy to test it and make sure it works too 🙂

      BTW this also spits out the 'header' from L3# but if this formula will go in L4 then something more like this:

       

      =IFERROR(DROP(REDUCE("",L3#, LAMBDA(p,q, 
   HSTACK(p, FILTER(ComponentPNs[Component PNs], 
                    ComponentPNs[Top Level PN]=q,"")
          )
   )),,1),"")

      note: I edited it to add the IFERROR wrap to remove the error values make it look nicer 🙂

       

Resources