I have an inventory I do each month and have a fairly simple question but not sure what is the best way to complete this task. I have 3 columns. ItemNumber, ItemName, and TotalQTY. The first sheet is what I actually have on hand in the warehouse the second sheet is what our system says we should have. So far very simple. The problem is the system has items that we do not have in our warehouse and some items that are in the warehouse are not in the system. I want to merge the two sheets so it will have all of the data on one sheet while showing the TotalQTY of each so I can see the variance between the two. I understand that some rows will not have a variance since they will not be in both sheets. The end product I desire is columns ItemNumber, ItemName, TotalQtyWarehouse, TotalQtySystem, and Variance.
1 Answer
Try this:
- Copy the system sheet data into a new sheet. Rename the Qty column to "SystemQty".
- copy the ItemNumber and ItemName columns from the Warehouse sheet underneath the date in the new table
- Select all data and use the "Remove Duplicates" command on the Data ribbon to remove duplicates for ItemNumber and ItemName. With the Warehouse data below the System data that means that Warehouse duplicates will be removed and System data with quantity will be retained.
- add a column for WarehouseQty. That should be column D
- use a formula to lookup the WarehouseQty like this
=iferror(vlookup(A2,Warehouse!$A$1:$C$100,3,false),0)
- This formula will return a zero if the ItemNumber is not found in the Warehouse sheet. If you don't want a zero, use an empty text string
"" - If you want to fill the empty SystemQty cells with a 0, do it this way: Select the SystemQty column, hit F5 > Special > click "Blanks" > OK > type a zero > hold down Ctrl and hit Enter
- add a column for Variance and subtract one quantity from the other.
