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.
Another option using TOCOL-IFS:
=LET(
join, Production[Product] = TOROW(BOM[Kit]),
HSTACK(
TOCOL(IFS(join, Production[Customer]), 2),
CHOOSEROWS(BOM, TOCOL(IFS(join, SEQUENCE(, ROWS(BOM))), 2))
)
)To exclude the Qty column from the output, change the array argument of CHOOSEROWS from BOM to BOM[[Kit]:[item]], or use INDEX and specify the [column_num]s to be included:
=LET(
join, Production[Product] = TOROW(BOM[Kit]),
HSTACK(
TOCOL(IFS(join, Production[Customer]), 2),
INDEX(BOM, TOCOL(IFS(join, SEQUENCE(, ROWS(BOM))), 2), {1,2})
)
)Cheers!
Thanks a lot for your solution that also works perfectly.
It's great to get inspiration from both you and OliverScheurich.
I can't judge which solution is best. Now I will have to study both formulas to learn how they work in detail, so I hopefully will be able to do similar magic myself in the future.