miltilevel Bill of Material

hello,
i have attached a file as sample. I need formulas to calculate the yellow highlighted fields.
thanks

plz check now if it is uploaded

Don't know if there is any shortcut, but I have to first breakdown the sub-assembly components into their mono ingredients, then add these to the main assembly items.

this will not work, i added only one formula but actually there will be 1000. there should be a database in the first sheet and the 2nd sheet you select which formula you need the details

Clean all expected results.

Details!E3

Please Login or Register  to view this content.

Details!H3

Please Login or Register  to view this content.

Hello Windknife,

Thanks for your reply, I couldn't follow your formula; it seems you are an expert

The problem is more complex; it is a multilevel formula where different assemblies may form one assembly.
i have attached another version which is more complex. this can be done without VBA?

thanks again.

Ethyl Acetate and Ethyl Alcohol are Assembly, but they don't find Mono data.

Change D31 and D32 to Mono. You can get the answer.

even if you change these to mono, still NC DLX3-5 (1/16) 70:30 & NC DLX3-5 (1/16) SOLUTION. check the file plz

answer is wrong. delete...

how to modify i am not that much expert

6 level

Please Login or Register  to view this content.

You can compare 5 level and 6 level to learn how to modify.

kindly check if this is correct:

=LET(
a,CHOOSECOLS(FILTER(Re,Re[Components]=Details!C2),2,3,4),
b,DROP(REDUCE("",SEQUENCE(ROWS(a)),LAMBDA(m,n,VSTACK(m,IF(INDEX(a,n,3)="Assembly",LET(z,CHOOSECOLS(FILTER(Re,Re[Components]=INDEX(a,n,1)),2,3,4),HSTACK(INDEX(z,,1),INDEX(z,,2)*INDEX(a,n,2),INDEX(z,,3))),INDEX(a,n,))))),1),
c,DROP(REDUCE("",SEQUENCE(ROWS(b)),LAMBDA(m,n,VSTACK(m,IF(INDEX(b,n,3)="Assembly",LET(z,CHOOSECOLS(FILTER(Re,Re[Components]=INDEX(b,n,1)),2,3,4),HSTACK(INDEX(z,,1),INDEX(z,,2)*INDEX(b,n,2),INDEX(z,,3))),INDEX(b,n,))))),1),
d,DROP(REDUCE("",SEQUENCE(ROWS(c)),LAMBDA(m,n,VSTACK(m,IF(INDEX(c,n,3)="Assembly",LET(z,CHOOSECOLS(FILTER(Re,Re[Components]=INDEX(c,n,1)),2,3,4),HSTACK(INDEX(z,,1),INDEX(z,,2)*INDEX(c,n,2),INDEX(z,,3))),INDEX(c,n,))))),1),
e,DROP(REDUCE("",SEQUENCE(ROWS(d)),LAMBDA(m,n,VSTACK(m,IF(INDEX(d,n,3)="Assembly",LET(z,CHOOSECOLS(FILTER(Re,Re[Components]=INDEX(d,n,1)),2,3,4),HSTACK(INDEX(z,,1),INDEX(z,,2)*INDEX(d,n,2),INDEX(z,,3))),INDEX(d,n,))))),1),
f,DROP(REDUCE("",SEQUENCE(ROWS(e)),LAMBDA(m,n,VSTACK(m,IF(INDEX(e,n,3)="Assembly",LET(z,CHOOSECOLS(FILTER(Re,Re[Components]=INDEX(e,n,1)),2,3,4),HSTACK(INDEX(z,,1),INDEX(z,,2)*INDEX(e,n,2),INDEX(z,,3))),INDEX(e,n,))))),1),
g,DROP(REDUCE("",SEQUENCE(ROWS(f)),LAMBDA(m,n,VSTACK(m,IF(INDEX(f,n,3)="Assembly",LET(z,CHOOSECOLS(FILTER(Re,Re[Components]=INDEX(f,n,1)),2,3,4),HSTACK(INDEX(z,,1),INDEX(z,,2)*INDEX(f,n,2),INDEX(z,,3))),INDEX(f,n,))))),1),
h,DROP(REDUCE("",SEQUENCE(ROWS(g)),LAMBDA(m,n,VSTACK(m,IF(INDEX(g,n,3)="Assembly",LET(z,CHOOSECOLS(FILTER(Re,Re[Components]=INDEX(g,n,1)),2,3,4),HSTACK(INDEX(z,,1),INDEX(z,,2)*INDEX(g,n,2),INDEX(z,,3))),INDEX(g,n,))))),1),
i,DROP(REDUCE("",SEQUENCE(ROWS(h)),LAMBDA(m,n,VSTACK(m,IF(INDEX(h,n,3)="Assembly",LET(z,CHOOSECOLS(FILTER(Re,Re[Components]=INDEX(h,n,1)),2,3,4),HSTACK(INDEX(z,,1),INDEX(z,,2)*INDEX(h,n,2),INDEX(z,,3))),INDEX(h,n,))))),1),
j,DROP(REDUCE("",SEQUENCE(ROWS(i)),LAMBDA(m,n,VSTACK(m,IF(INDEX(i,n,3)="Assembly",LET(z,CHOOSECOLS(FILTER(Re,Re[Components]=INDEX(i,n,1)),2,3,4),HSTACK(INDEX(z,,1),INDEX(z,,2)*INDEX(i,n,2),INDEX(z,,3))),INDEX(i,n,))))),1),
uniqueItems,SORT(UNIQUE(INDEX(j,,1))),
DROP(REDUCE("",uniqueItems,LAMBDA(m,n,VSTACK(m,HSTACK(n,SUM((INDEX(j,,1)=n)*(INDEX(j,,2))))))),1)
)

Good job, windknife! Here's a Rep for you!

Just want to add. You can put a formula to Ingredient Type in column D: =IF(ISNUMBER(MATCH([@Ingredients],[Components],0)),"Assembly","Mono")

Your formula is very well.

@josephteh, thanks for your Rep and suggestion.

You are welcome, windknife!

thank you both Windknife and Josephteh.

i have attached athe file with few things to adds, i think it should be easy for you guys to solve it.

thanks in advance.

See attachemen for seeing details.

@camS74, just want to add one thing.. you could use a spill formula in helper column L:
=CHOOSECOLS(XLOOKUP(I3#,Re[Ingredients],Re[Ingredient Type],""),1)

Josephtech, thanks.the helper not required anymore since was not used in Windknife formula.
Windknife, plz check the attached file with more request. I tried to do it but somehow failed.

I don't know what you want. By the way, valid can't refresh automatic without VBA.
I think you must ask one question in one post, don't drip-feeding your qestion.

you mean for the same file i should post different posts?
if you can check the file that I have attached V4. it is the same your formula, but I want to exclude "Non-Solvent" from the formula; is it possible?

Try this,

Please Login or Register  to view this content.

What I mean is that you should tell all the problems at the beginning post.

This is working . The total percentage can be added? Since if I add in a different formula and if the table size expands, I will get an error.
Honestly, when I started this thread, I didn't expect someone to have this knowledge, and my idea was expanded.

Try this,

Please Login or Register  to view this content.

worked, but we need to change the remaining 3 formulas to have the same total percentages. i highlighted them in the attached file.

see attachement.

thanks, you are the king of Excel

You are welcome. ：）
I am not a king of excel. I learn excel from the gurus of this forum.
-----------------------------------
If that takes care of your original question, please click on "Thread Tools" from the menu link above and mark this thread as SOLVED.

Hello and Happy New year. do you know someone who can convert my file to web app. of corse many other function formulas to be added. with formas and data entry, stock and prices... i don't mind if it will cost me something to develop it.

I don’t know any engineers who have the time to help write web apps.
Maybe the Gurus of this forum know such people.

Happy New Year too you.

Thanks you