Combing a Pivot table and V lookup table to create a daily inventory report

[sayoung4517]

Right now I get a daily report which is an excel dump of 1000's of parts and the daily requirement for that part #'s work order. depending on the number of work order calling for that part, the same part # could show up a dozen times or more. I copy out the part numbers that I need and put them on a separate spreadsheet. I then create a pivot table which gives me the part number date and daily material requirement. This would be fine except I have to convert the material requirement for each part from Square inches to Lineal Feet. I use a table I created and v lookup to assign the correct number to divide by to perform the conversion. I ended up copying my pivot table and pasting it as values, then inserting columns to hold the result of the calculation derived from the results of the lookup. I thought I could use the formula I read about in power pivot, but no luck. Could someone point me in the right direction. I don't mind doing the work but I think I'm missing an easier solution.


Thanks


Scott

 

[keymaster]

@Scott..


Welcome to Chandoo.org and thanks for your posting your question. I suggest you to know our forum rules better by reading 3 sticky posts on the home page - http://chandoo.org/forums


Also, Please join our newsletter so that you can get latest tips & tutorials by email. Visit http://chandoo.org/wp/subscribe/


Coming to your question:


You can solve this in multiple number of ways.


Option 1 - using VLOOKUP in a column adjacent to Pivot


This is similar to what you are doing today. But instead of moving the data to another sheet, keep it in pivot and use the lookup formulas in the next column outside pivot. This way, when you have new data, you just need to refresh the pivot.


Option 2 - Using Power Pivot


This requires that you are using Excel 2010 or above and have enabled PowerPivot add-in. If that is the case, you can set up both work orders table & size mapping table in power pivot and establish a connection between both. Once that is done, you can use simple formulas to fetch conversion ration to workorder table. Then, create a measure that gives the multiplication of actual size * conversion factor and you are done.