Total rows
0
Distinct SKUs
0
Matched in Audit
0
Missing in Audit
0
Zero-qty retained
0
Adj Plus
0
Adj Minus
0
Not Enough Qty
0
0 of 0
| SKU | Lot | Exp Date | Avl Qty | Audit Qty | Status Adj | Indicator | Variance | Result |
|---|---|---|---|---|---|---|---|---|
| No rows match the filter. | ||||||||
Missing in Qty File
Products listed in Audit but not found in Qty per Location per Lot.
All audit products are present in the Qty file.
Sistem Qty Mismatch
Audit Sistem ≠ Sum of Available Qty (pivot) for the same Product.
All Sistem quantities match the pivoted Qty.
Blank Lot / Serial — Pivot
Rows from the Qty file where Lot/Serial Number is blank, grouped per Product + Exp Date.
No blank-lot rows in the Qty file.
Missing in Audit File
Products present in 'Final Qty Product per Lot' (pivot) but NOT found in the Audit file.
Every pivoted product has a matching audit entry.
Problem or Double SKU Product
Audit file contains the same Product on more than one row. These SKUs are excluded from the Final Result above to avoid an ambiguous VLOOKUP. Fix the source file (merge or remove the duplicate row) and re-upload.
No duplicate SKUs in the Audit file.
Not Enough Qty — Details
SKUs where total positive Avl Qty across all lots can't cover the audit shortage.
No Not-Enough-Qty SKUs
Every audited SKU has enough positive Avl Qty across its lots to cover the requested adjustment. Nothing needs attention here.
Tips
- The logic (confirmed against the Guide sheet):
- • Adj Minus (Audit Qty negative = shortage): deduct from lots top-to-bottom. Earlier lots get fully zeroed out ("Next") until the running total covers the shortage; the lot where it tips over gets a partial deduction ("Stop") and keeps the leftover.
- • Adj Plus (Audit Qty positive = excess): skip every lot except the last one of that SKU group, and dump the entire excess onto that last lot ("Adj").
- Workflow:
- 1. In Odoo, group by Location then Product, sort by oldest Exp Date.
- 2. Export Location with Lot and Exp Date — this is the "Qty per Location per Lot" file.
- 3. The app pivots it into one row per Product + Lot + Exp Date.
- 4. Upload your "Audit Qty" file (Product, Sistem, Fisik, Selisih).
- 5. The app VLOOKUPs Audit Qty into the pivot and classifies Adj Plus / Adj Minus.
- Tip: filter out negative-lot rows in Odoo first, or rely on the app's positive-qty filter in the pivot step.
Downloaded workbook contains: Final Result, Adj Plus, Adj Minus, Missing in Qty File, Sistem Qty Mismatch, Missing in Audit File & Tips (visible), plus hidden helper sheets Raw Data, Final Qty Product per Lot, Audit Qty, and Formula Audit. Hide toggles apply to the Final Result sheet; format = live formulas.