Matching by batch and serial number when part numbers repeat

A reconciliation that matches part number to part number can show a perfect result and still hide an error. If a part is spread over three lots and the count has the right total but the wrong lots, nothing in the total shows it. The mismatch appears later, when a quality hold targets a lot that is not where the record says it is, or when a warranty claim names a serial number nobody can find. Deciding at which level to match is the first step.

Three levels of matching

• Item level: quantity per part number. Enough for bulk, low-value, interchangeable goods such as screws or packaging.
• Lot level: quantity per part number and lot (batch). For goods where quality, expiry or traceability by batch matters, such as adhesives, chemicals, food or electronic components.
• Serial level: one row per serial number. For high-value items with warranty and individual tracking, such as devices, motors or instruments.

It is normal for one warehouse to match screws by item, chemicals by lot and motors by serial number. Choose the level for each item group, and base the choice not on convenience but on what you would lose if the wrong lot or serial were used.

How totals hide errors

For illustration, item P-210 has two lots. The book shows 300 in lot A and 200 in lot B, 500 in total. The count finds 200 in lot A and 300 in lot B, also 500 in total. An item-level reconciliation says "matched". At lot level, A is -100 and B is +100. The cause may be that cartons of lot B were put on the rack of lot A, or that an issue from A was booked against B. If lot A is on quality hold, the record points at 300 pieces, 100 of which are not there, while 100 pieces of lot B, which should not be affected, sit in the wrong place.

Building the key

• Use a key made of part code and lot (or serial), for example P-210|A. Store it as text, and normalise it: trim spaces, unify case, replace look-alike characters and settle the use of hyphens.
• The count must capture the lot. Scan the lot barcode, or read it from the label and enter it, and take a photo when it is printed only as text and is hard to read.
• The book data must contain the lot. If the system keeps lots in a different table, join them before reconciling.
• In the reconciliation file this key is the matching column, and differences are calculated per key.

Serial numbers: one row each

Compare lists, not quantities. There are three outcomes.

1. In both: matched.
2. In the book but not in the count: suspect a typing error first, then search for the item.
3. In the count but not in the book: an unrecorded device, or a typing error.

Check both lists for duplicates as well. Common traps are O and 0, I and 1, leading zeros lost in Excel, hyphens and spaces, and the difference between the supplier's serial and your internal one. Note which one the book uses. Scan serial numbers; do not type them.

When the lot cannot be read

• Create a "lot unknown" entry for each part, count it, and photograph the label. Do not guess.
• If the label is partly worn away, record the readable part, take a photo and mark the line "partly read".
• After the count, agree the treatment with quality, purchasing and finance. The goods may be held until they are identified. Never move them into an existing lot just to make the figures fit.
• For the future, make sure the internal label printed at receiving always carries the lot number.

Summary

A matching quantity is not the same as a matching lot. Fix the level for each item group, build the key and set unreadable lots apart, and the reconciliation becomes one step more exact. Smart Inventory matches count results with your records line by line at item, lot or serial level, as agreed, and puts the lines it cannot link on a separate list.