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 🙂
bgeorge11
Aug 24, 2023Copper Contributor
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.
OliverScheurich
Aug 25, 2023Gold Contributor
Does it work in the attached file?