Forum Discussion
bgeorge11
Aug 23, 2023Copper Contributor
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...
- 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 🙂
Patrick2788
Aug 23, 2023Silver Contributor
This is scaled down from your example but gives you the idea:
=FILTER(Table2[Name],ISNUMBER(XMATCH(Table2[Category],L1#)))
- bgeorge11Aug 24, 2023Copper ContributorI tried this previously but I need to have the results spill into separate adjacent columns, rather than a single column, dependent upon the top level part numbers in the L3# heading, as seen in Step 5.