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 🙂
PeterBartholomew1
Aug 25, 2023Silver Contributor
This is based upon the file provided by OliverScheurich
My worksheet formula is presented as a Lambda function
= ComponentListλ(selectedTopLevel)
where ComponentListλ is defined to be
= LET(
sortSelected, TOROW(SORT(inputList)),
lists, REDUCE("", sortSelected,
LAMBDA(acc, selected, HSTACK(acc, FILTER(component, topLevel = selected)))),
VSTACK(sortSelected, DROP(IF(ISERROR(lists), "", lists), , 1))
)