I've ran into a bit of an issue and I'm stuck.
Background:
I'm trying to set up an inventory system for which I have unique part numbers that correspond with specific lot numbers and piece quantities. The lot numbers can overlap with multiple part numbers and are not unique.
When I first receive these parts - I want to check them into a raw goods list. When I finish the parts, I want to generate a finished goods list and remove them from the raw list. This will tell me how many raw goods I have available for that part number and it's corresponding lot number.
Issue:
Pivot tables are amazing. I can check in the raw goods, compile the data into a pivot table and then compare that to a pivot table of my finished goods.
What function / equation can I use to calculate the total remaining of each part number and it's corresponding lot number? Both criteria from my finished goods list must match the raw goods criteria in order for this output calculation to occur.
I've attached a link for a reference sheet of what I'm working on. Please note that there's much more to it than what I've included, but for simplicity and privacy I've removed all but the relevant information.
Google Sheet Link - Fix Me!
Also, I'm new here. If there's a better section to post this question (since it is relevant to Excel pivot tables as well!) please let me know or feel free to move it (if possible)!
Thanks!
Background:
I'm trying to set up an inventory system for which I have unique part numbers that correspond with specific lot numbers and piece quantities. The lot numbers can overlap with multiple part numbers and are not unique.
When I first receive these parts - I want to check them into a raw goods list. When I finish the parts, I want to generate a finished goods list and remove them from the raw list. This will tell me how many raw goods I have available for that part number and it's corresponding lot number.
Issue:
Pivot tables are amazing. I can check in the raw goods, compile the data into a pivot table and then compare that to a pivot table of my finished goods.
What function / equation can I use to calculate the total remaining of each part number and it's corresponding lot number? Both criteria from my finished goods list must match the raw goods criteria in order for this output calculation to occur.
I've attached a link for a reference sheet of what I'm working on. Please note that there's much more to it than what I've included, but for simplicity and privacy I've removed all but the relevant information.
Google Sheet Link - Fix Me!
Also, I'm new here. If there's a better section to post this question (since it is relevant to Excel pivot tables as well!) please let me know or feel free to move it (if possible)!
Thanks!
