Hello everyone,
Thanks in advance for looking. I am looking to dust off my Excel skills that have laid dormant for a few years, so would appreciate any help that may be on offer...
I am trying to create a relatively simple product assembly price list from an export I made from our now defunct MRP software.
I have a list of product assembles which contains multiple rows for each product (one row for each component or raw material assembly) and I wish to compile this into a multi tabbed spreadsheet.
I have attached an example of how the data is laid out.
There are several hundred different assemblies but the data all follows the same pattern:
Product Code | Component | Price
Ideally, I wish to separate the data so there is a second tab that only shows the total cost price for each assembly (so that the product code only shows once).
Note that each product may have a different quantity of parts, so some kind of LOOKUP function is needed to reference the first column containing the product code.
Excel Query.jpg
Secondary to this, I will be looking to setup a Raw Material tab which will contain the prices for materials used in the assemblies.
I have this list, but it would need to be cross referenced from the main assembly list as these prices fluctuate.
Tab 1 - Raw Materials list (prices updated occasionally manually)
Tab 2 - Assemblies made up of the raw materials (the raw materials are listed in my data, but I need to create 'new' versions that references to the first tab)
Tab 3 - Simplified list of completed assembly prices taken by refencing the assemly data in tab 2
Happy to elaborate further if anything isn't clear.
Hope you guys might be able to point me in the right direction!
Bookmarks