Forum Discussion
Solved
BOM explosion - repeating sequence of arrays
I have 2 tables, Table 1 and Table 2 Table 1 shows the products I have to make, Kit 1, 2 and 3. Table 2 shows the items used to make the kits. In the resulting table I would like a list of the 4 ...
=IFNA( VSTACK( HSTACK("Customer","Product","Item","Qty"), DROP(REDUCE("",SEQUENCE(ROWS(Production)), LAMBDA(u,v, VSTACK(u, LET(rws,ROWS(FILTER(BOM[Kit],BOM[Kit]=INDEX(Production[Product],v))), HSTACK( EXPAND(INDEX(Production[Customer],v),rws,,INDEX(Production[Customer],v)), FILTER(BOM[[Kit]:[Item]],BOM[Kit]=INDEX(Production[Product],v))))))), 1)), "")This returns the intended result in my sample file.
=IFNA(
VSTACK(
HSTACK("Customer","Product","Item","Qty"),
DROP(REDUCE("",SEQUENCE(ROWS(Production)),
LAMBDA(u,v,
VSTACK(u,
LET(rws,ROWS(FILTER(BOM[Kit],BOM[Kit]=INDEX(Production[Product],v))),
HSTACK(
EXPAND(INDEX(Production[Customer],v),rws,,INDEX(Production[Customer],v)),
FILTER(BOM[[Kit]:[Item]],BOM[Kit]=INDEX(Production[Product],v))))))),
1)),
"")This returns the intended result in my sample file.
Thanks for your solution. It's so advanced that my brain hurts looking at it.
Certainly I will spend time looking at the formula trying to understand how the individual parts works. It get's difficult to debug and understand when several array-formulas are combined into one formula..
