Keep Pivot Table from combining like entries in one column if other column doesnt match.

[kendrick.meiners]

My company recently performed a full physical inventory of all of our aircraft parts. Now I am trying to reconcile the count sheet data with the quantities our inventory system thinks we have. I started by transcribing the count sheets into an excel file, and so the first column is the part number, and the second column is the quantity found. The bin location and description can be left out for these purposes as the product I am trying to come up with is merely a count for each part number that can be entered into the system's update module. However, my accounting department requires me to show the work on a spreadsheet and prove that the value change we expect is what comes out on the final report so a bank auditor will be satisfied there were no errors in the process.

Now, there are quite a few instances where the same part number might have been found in multiple locations. Therefore, I used a pivot table to combine like entries of the part number column, and show me the sum of the actual quantity found for each so I get a total quantity our company owns for each line item. I will call this data my Total Count for the purposes of this question.

I then copied that information onto a new worksheet, and had the inventory system produce me a report of what part numbers we have and what quantities it thinks we have, and also what the unit cost of each line item is. I then added the part numbers below my Total Count data in the same column, and added new columns for the System Quantity and Unit Cost. So my four columns on the new sheet read as Part Number, Actual Qty Found, System Qty, and Unit Cost. The first 6000 or so line items are from my Total Count, so they have part numbers and Actual Qty Values, but I entered Zero for the System Qty and Unit Cost Columns. The next 6000 or so lines have Part Numbers, Zeros entered for Actual Qty, and values for System Qty and Unit Cost. I created a new pivot table to try to combine like part numbers so it would show me what quantity was found, what the system thought we would find, and what the unit cost was. From there I was hoping to be able to just use a simple multiplication formula to show extended expected value and extended actual value for each line item, take the totals of those columns, and show what the total value change would be for the inventory. However, this did not work as expected and I realized my logic was flawed.

After seeing that the total extended expected value did not match the same total from the original report I had the system generate, I realized what was going wrong. Say someone had purchased Qty 5 of part number ABC in the past at a cost of $5/each. Then another time someone else purchased Qty 10 of part number ABC at $10/each and didn't use the same record in the inventory system, so now there are two instances of part number ABC with two different quantities and unit costs on my report. If I have the pivot table take the sum of the Unit Cost column, it would tell me I should have Qty 15 valued at $15/each which obviously isn't correct. If I have it take the average of the Unit Cost column, it will say I have Qty 15 at $7.50/each which also isn't correct. Attached is an example of the kind of data I have in this table in case that helps explain it.

So I am thinking a Pivot Table might not be the answer for this since I don't know how I would keep it from combining part numbers if the unit cost values are different. Unfortunately, I am not that well versed in some of the more complex functions and macros on Excel, so I was hoping someone might be able to help me with this. Thank you for your time in reading this, and any advice would be greatly appreciated.



Regards,
-Kendrick

[MarvinP]

Hi Kendrick,

I think you are asking for a Value Filter by Part Number. See the attached where I've done that and also look at this YouTube that gives another example. I think you want to filter out all those 0 number of parts.

https://www.youtube.com/watch?v=uXuBCx_4PxE

[kendrick.meiners]

Hello Marvin,
Thanks for the prompt reply. I took a look at the example spreadsheet you linked, but it is still showing the same problem. Note that for the first line item, Part Number S9412-007, that it got the two quantity columns correct. We found 2 in stock, the system thought we had 5 spread across two line items, so that all makes sense. The issue is in the unit cost. The system had thought we had 2 valued at $0.50/ea and another 3 valued at $1.00/ea. But in the pivot table, it shows it as all now being at $1.50/ea, which is falsely inflating the value of the inventory. And that is what is creating my discrepancy on my final step. It is increasing the value of my inventory by about a million dollars. I can't get the total value of my inventory to line up with what the system shows we have because of that error. Is there anyway to keep those as separate line items because the unit costs are different, while still combining all the lines where the unit costs are the same? I could do it manually in theory, but the full product is a spreadsheet of about 10,000 line items so I'm not thrilled about that idea.

-Kendrick

[MarvinP]

Hi Kendrick,

See the attached where I've done a formula outside the table that may be what you want.