Forum Discussion

bgeorge11's avatar
bgeorge11
Copper Contributor
Aug 23, 2023

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

     

Resources