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 products to make from table 1 exploded with the items to use in each kit.
It's quite similar to a previous thread about a repeating sequence of arrays, the main difference that each row from table 1 have to be repeated a different number of times depending on items in table 2.
Any help that guide me in the right direction is very much appreciated.
I know how to make this in VBA, but I think it can be done using array formulas which I prefer as they expand automatically of rows in table 1 or 2 changes.
=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.
4 Replies
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.
=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..
