• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data lookup and aggregation

N

niting

New Member
Hie forum,


I tried to upload the sample worksheet, but just could not. Therefore, posting my question in text form.


I have a sheet which has the Bill of Material details for around 50 FG. The FG code is in column A whereas relevant components like SFG, Chemicals, Fuel, Packing material, Raw material codes is in row B. The respective cells are populated with figures required to produce 1 ton of FG.


I also have a sheet which details the BOM for SFG with SFG codes in Col A and releavnt components like SFG, Chemicals, Fuel, Packing material, Raw material codes is in row B. The respective cells are populated with figures required to produce 1 ton of SFG.


I now want that a new sheet be created with FG codes in column A and basic components like Raw material, fuel, chemicals and packing material in the row. That is, I want this final sheet to account for all inputs that goes towards making SFG used to produce FG and also materials used independently used to produce FG in combination with SFG and populate the cells with final figures.


In this manner, I will be able to arrive at final input output ratio. Please also note, that there are cases where one FG is used in production of other FG and one FG is used to produce another SFG.


Please offer your suggestions to help me through this problem.


Thanks

Nitin Gupta
 
niting,


I suggest you to upload a sample file using either Dropbox.com or speedyshare.com. Just upload the file and paste its link so that i could be downloaded by any one here. It is difficult to visualize problem by textual description.


Keep posting,


Regards,

Faseeh
 
Hi Faseeh,


Please see the sample work book in the link-:


https://www.dropbox.com/s/j5szm0wwwmmx78s/Multilevel%20Bom-QUERY.xls


Thanks
 
Hi niting,


You have Codes in Row 2 that you want to be in col A in the final report?? and then you want the current data in Col A to be in Row 2?? Simply you want to transpose the table?? Is that correct?
 
Faseeh,


Since the sheet is now available, I will talk in reference to the sheet contents.


In Tab " Mapping" there are two set of data. One is BOM for FG in range A1: AV100, The BOM is for material codes in Col A and the constituent materials are in Row 3.


In Tab " Mapping" only, in range A103:CC190, there is BOM for SFG, the material codes which are appearing in COl A and the constituent materials are in Row 3.


Now, what I want is that in tab Sheet 1, the FG material codes be there in Col A and the final raw materials/constituents come in row 3. essentially, if 100 tons of SFG is required in production of 100 tons of FG and 100 tons of raw material is required for production of 100 tons of SFG, then against the FG, the raw material quantity should be 100.


Also, there are quite a few cross linkages wherein for production of one FG, another FG is required, so the quantity of constituents should be the total figure which would be utilised in production of SFG and FG used in the production of final FG.


I hope I have been able to explain my problem.


Thanks

Nitin
 
niting,


Sorry for late reply. Just below the table you are referring to is another table with codes on the column A. Do you want this table. Let me upload what i have prepared.


Regards,
 
Hi Nitin ,


From what I have understood , your application is a complex one , especially in view of your data format , which is in the form of a matrix.


One item , let us say 710015 , requires 100 tons of 610040 ; 610040 in turn requires 111 tons of 610033 , 610033 requires 118 tons of 610003 ; in case there are more levels , I think the logic required will not be possible using a formula based solution , since it cannot accommodate this complexity and variability.


VBA can certainly do what you want done , but even there , since it is a recursive search , it will take time to develop.


Anyway , you can see what Faseeh has developed , and decide whether that gives you what you are looking for.


Narayan
 
Hi nitin,


Here is the sample file i worked out but let me say that firstly, I am unable to understand how you will connect the two tables on the mapping sheet.


On the first table, the Codes that are present in the col A are not present in Table B, neither in the header row nor in the portion of Col A in that table: See this file


http://dl.dropbox.com/u/60644346/Copy%20of%20Multilevel%20Bom-QUERY-WORKOUT.xls


Lets assume that i pick up a Col A Value and and header row value from the second table, I can find the col value in the third row in the first table, then where i can find the Header Row Value of second table in this first table??


Regards,
 
Faseeh and Narayan,


Thanks for your inputs.


Faseeh, I am afraid the solution proposed by you does nto answer my queries. The query is aptly captured by Narayan.


Hope the Excel Ninzas would be able to help me out.


Thanks

Nitin Gupta
 
Hi Nitin ,


If someone has to develop a VBA solution to your problem , can you confirm whether the file you have uploaded is the actual working file , or it is a sample file ?


If at all anything is going to change , what will the changes be ?


The basic variability is :


1. The number of items in both tables can vary , both across and down.


Can you also confirm whether there is any item which will need multiple inputs ; I have not gone through your data in detail , so I cannot conclude. For example , if an item , let us say 710015 , requires 'x' tons of A , and 'y' tons of B , and A and B in turn require 'm' tons of C and 'n' tons of D ,.... , then the level of complexity increases ; it will be slightly easier if you can confirm that every item will have exactly one input.


Narayan
 
Narayan,


The file posted is Bill of Material drill down file and it is the working file not sample file. There is no chance that the inputs will change.


However, going forward new Finished goods could be added or new constituent inputs can be added like packing material or chemicals. This is the only variability factor but that will be all additional and not going to affect the current data.


Narayan, yes, the sheet involves multiple inputs. For production of 100 tons of FG 710099, 72 tons of SFG 610025 is required, 98 tons of 120003, 65 tons of 120094 and 195 tons of 120109 chemicals are required.


For production of 100 tons of SFG 610025, 25 tons of 120000, 500 tons of 120004 and 100 tons of 120006 is required. Also required for production of 100 tons of SFG 610025 is 100 tons of SFG 610014.


For production of 100 ton of SFG 610014, 300 tons of 120000, 80 tons of 120003 and 200 tons of 120019 chemical is required. Also 100 tons of STP feed cake is required.


So effectively in sheet 1, against FG 710099 in Column A, against chemicals in row 3, what is required is final figure after taking into account interlinkages.


Sorry to be a pain!!


Thanks

Nitin
 
Hi Nitin ,


Thanks for the clarifications. I have followed through on your example , and effectively , the chain for 710099 is :

[pre]
Code: 
710099	610025	72	120000	18
120004	360
120006	72
610014	100	120000	300
120003	80
120019	200
Feed Cake	100
120003	98
120094	65
120109	195
So , the final table will be as follows :

120000             318
120003             178
120004             360
120006               72
120019             200
120094               65
120109             195
610014             100
610025               72
Feed Cake        100
[/pre]
Can you confirm whether this is correct ?


Narayan
 
Hi Nitin ,


I tried to code your application , but it's beyond me. I am not able to code it based on the format of your data ; probably if the data is reorganized , it may be possible.


What I have done is used formulae to do something ; see if it is helpful.


http://speedy.sh/mpZdk/Multilevel-Bom-QUERY.xlsx


I have seen it works for the following codes : 710002 , 710003 , 710004 , 710008 , 710009 ; when you try it out for the other codes , you will see why it doesn't work.


What I can think of is to segregate the codes into several categories , and have separate sheets for each of the categories , since the formulae will have to be tailored for each category.


Let me know your ideas.


Narayan
 
You must log in or register to reply here.
Back
Top