Hello,
I work for an engineering organisation and have a large data set (50,000) of parts and sub assemblies. My main objective is to work out the material portion of the sub-assemblies and ultimately the kits which are sold to the customers. For example Part 1 is 50% aluminium 25% steel and 25% copper.
The data set is an extract of the data which i am working with.
Column A and B show the level of the parts (there are two examples here Part 1 & Part 2 and these subsequent components).
Column C & D shows the value by percentage of the higher level assembly (for example Part A1 is £100 and Part A1A is £37 or 37% of the value and hence is 37% aluminium Part A1A is 19% of the value of Part A)
What i am trying to develop is a dynamic formula which will fill in row 5 - J to P with the material percentage that makes up the kit (for example aluminium in AT2 should state 77%) but i also require the formula to be dynamic and work for Part 2 row 14 - J - P where there is only 2 parts which make up the assembly and then work out the higher level assembly shown in row 3. The data set i am using is huge so some assemblies will have 2 parts and others will have 200.
The formula i have used in J5 is flawed (or i am unable to build upon it any further to make it work). To start the nested if statement I was using =IF((AND(G5="X", G6="")) to show the start of a sub assembly and then an index and match statement on column D to define how many rows were included but i think this is flawed or needs expanding to take into account there are multiple materials. Furthermore once I begin adding sub assemblies together.
FYI i have a VBA programme which will input the formula into the required lines once i have a working formula.
Also I am on a HP PC and running Windows 10, and Excel version 14.0.7
Hopefully this is clear. Thank you in advance for your help
TJ
Hello Tom
I am a bit confused with the layout of the spreadsheet at the moment, so if you can clarify the following
• I get the 1/2/3 in Column B, but what are the numbers in Columns E,F,G & H for (2 2's?)?
• J2, J4 & J13 are using data from Columns C & J, are the first 4 columns from somekind of database?
• Where do the 100%'s come from in columns J to P?
The formula in K2 is looking at column D - whereas everything else is looking at C - is that just a typo?
Purfleet
Certainly not the finished article but this seems to work on the limited data set you have attached
=IF($B3=1,SUMPRODUCT(--ISNUMBER(SEARCH(TRIM(LEFT($A3,7)),$A$2:$A$16))*($I$2:$I$16=J$1)*$C$2:$C$16),IF($B3=2,SUMPRODUCT(--ISNUMBER(SEARCH(TRIM(LEFT($A3,7)),$A$2:$A$16))*($I$2:$I$16=J$1)*$D$2:$D$16)))
No idea how it will perform in terms of reliablity or speed, but we might be able to tweak it after some feedback
Hello Purfleet,
Thank you for your prompt response.
My formula was a typo so please except my apology. What you have done works however i do not think it I can use as if i am correct the trim function is using column A for something (not sure what?) but i added this in in redaction of product codes so it is not applicable to use (unless it is defining the length of the list?).
I have attached a slightly amended data set with four examples to reduce confusion.
Examples 1 - 3 are based upon base level components. Column D denotes cost of the sub-assembly and column E is the value of the base components. This is where the data in column G comes from as this is the value by % of the sub assembly it goes into. Column L to R is the level % of material which makes up the component. For the base level components these will be 100% but the sub assembly will be a mixture depending on what it is made from.
For example 4 this is two sub-assemblies making a high level assembly. This is where it will probably make more sense for columns L to R as if you look along the row, for example 34, you will see the total % = 100% but you can see in column F the value of the % of the above assembly is 50%. This is why it is important to have the formula * the material columns (L to R) with column F so that the same formula is applicable with both examples.
Lastly because the length of the data set is different in each example the formula would ideally need to be dynamic in the sense it defines the length of the each data set. My thinking was in example 1 to use a IF statement on the X in column I2 and the fact the column below I3 is blank to identify a sub assembly and then to use the match and index function on column G to define the length of the data set. I was thinking it would then require another if statement to look at column K and if the material is the same as the column title (for example in L2 Ally it would only complete the sum including the rows in column K with the name Ally) for Example 1 it would identify row 3 and 4 and put the following sum into the formula =(G3*L3)+(G4*L4). This sum is essentially what i am seeking to obtain but because the large and dynamic nature of the data i require the formula to define the length of each data set and identify which rows it is applicable to within that data set.
Hopefully this is a bit clearer. Thank you so much for your help though it is greatly appreciated!
Tom
The LEFT($A2,7) takes the first 7 characters of a2, the trim gets rid of any spaces, the search looks for the result of left/trim in the rest of column A. –ISNUMBER takes all the previous and makes it a 1 or 0.
The middle part matched the Material into 1 & 0’s
Then they are multiplied by the percentage.
So you will get (small example)
(1,1,0,0,1)*(1,0,0,0,1)*(37,40,3,1,4) =
1*1*37 =37
1*0*40 = 0
0*0*3 = 0
0*0*1 = 0
1*1*4 = 4
SUMPRODUCT adds them together and makes 41
Its just a way oif matching the right parts but it only works if Part 1 is only in the list once.
It might be that VBA with a loop is the way to go, but i really need to know the esact way the data is laid out. For example on the attached example 1 & 3 are wrong as the formula is picking up both lots of Part A1, but Example 2 is okay.
Is the whole report 1 large list with Part A1 repeated multiple times? or is each product on its own worksheet?
