Report Duplicate s column data

Question

We re running a physical inventory and have the following situation. A part number can have quantities in multiple locations. We need to report each location quantities for the part number in a single line.

This is what we have Green and Red text show duplicates but with different locations and quantities.

PART NUMBER	location	Quantity
003-0690-03	Crib	5
003-0690-03	Dock	5
003-0690-03	Warehouse	5
003-0690-05	Crib	1
003-0690-09	Dock	3
003-0 701-01	Warehouse	6
003-0 701-13	Warehouse	4
003-0 715-03	Crib	2
003-0 701-01	Dock	6
003-0 701-01	Crib	6

Here is what we need to do

PART NUMBER	Crib	Dock	Warehouse	Total	Record QTY	Variance
003-0690-03	5	5	5	15	20	-5
003-0701-01	6	6	6	18	18	0

Answer 1

You can create a pivot table based on the data, or if you have Microsoft 365, use the new PIVOTBY function: