Courses
Missing deliveries
Background
There are deliveries missing during a certain period and you suspect a pattern. Therefor you want to compare:
- Purchase orders (data source: ERP).
- Goods receipts (data source: Third Party Logistics WMS).
Exercises
Exercise 1 [CONCAT]
Create a unique key in both sheets on Purchase order position level by joining Purchase order and Position.
Create a lookup of columns Goods receipt date and Goods receipt quantity from Goods receipts sheet into Purchase orders sheet using the keys.
Exercise 3 [IFERROR]
Enclose the formula with IFERROR to replace errors with double quotes (“”).
Exercise 4 [Conditional Formatting]
Use Conditional Formatting to make blank cells (“”) red.
Exercise 5
Find the pattern for the missing deliveries.
Table: TablePurchaseOrders and TableGoodsReceipts
Table Header: Key
= CONCAT([@[Purchase order]]; "-"; [@Position])
Exception: Formulas should in general be placed to the right of the raw data to avoid pasting over the formulas if updating the sheet. Keys is an exception since VLOOKUP searches in the leftmost column. Be sure to mark the added Table Headers with another color.
Table: TablePurchaseOrders
Table Header: Goods receipt date
= VLOOKUP([@Key]; TableGoodsReceipts; MATCH(TableGoodsReceipts[[#Headers];[Goods receipt date]]; TableGoodsReceipts[#Headers]; 0); 0)
Table: TablePurchaseOrders
Table Header: Goods receipt quantity
= VLOOKUP([@Key]; TableGoodsReceipts; MATCH(TableGoodsReceipts[[#Headers];[Goods receipt quantity]]; TableGoodsReceipts[#Headers]; 0); 0)
Table: TablePurchaseOrders
Table Header: Goods receipt date
= IFERROR( VLOOKUP([@Key]; TableGoodsReceipts; MATCH(TableGoodsReceipts[[#Headers];[Goods receipt date]]; TableGoodsReceipts[#Headers]; 0); 0); "")
Table: TablePurchaseOrders
Table Header: Goods receipt quantity
= IFERROR( VLOOKUP([@Key]; TableGoodsReceipts; MATCH(TableGoodsReceipts[[#Headers];[Goods receipt quantity]]; TableGoodsReceipts[#Headers]; 0); 0); "")
The result has been cleaned after using IFERROR and Conditional Formatting.
Result: When sorting the Goods receipt quantity it is more apparent that Requested delivery date is the common pattern for the missing deliveries.
Conclusion: Since the the deliveries comes from different suppliers it is low likelihood that all goods has gone missing in different transports at the same date. Investigating at Goods Receiving is a good start.