Lot traceability in Excel: three sheets, and how to follow a lot through them

The receiving sheet

The receiving sheet, or goods-in log, has one row for each lot delivered: the supplier, the supplier's lot number, your own lot number if you give one, the quantity and the date received. On its own it shows what arrived, not where it was used.

In TraceBench, load it as Lots or serial units recorded, with the production sheet, and choose the lot column that the production sheet also uses. Declare that the two columns share a numbering, and a batch traced backwards reaches the receiving row for each lot it used, with every other cell in that row, the supplier's lot number included, exactly as written.

The production sheet: lot in, lot out

The production sheet links what went in to what came out: one row for each lot used, with the batch it went into. Load it as Production: input into output, choose the column with the lot used and the column with the batch made, and give each a numbering name, such as supplier lots and finished batches, so the two are never confused. If several rows belong to one production run, choose its column too: every lot used in the run may relate to every batch made, and TraceBench labels that potential attribution rather than dividing quantities.

The despatch sheet

The despatch sheet records what left: one row for each batch in each shipment, with the shipment or delivery note number, the customer and the date. Load it as Despatch: item in a shipment, choose the batch column and the shipment column, and confirm that the rows record what was actually in each shipment, not a booking or a label request. The customer stays in the row, and the row shows with each link, exactly as written. A shipment number is also a starting point: traced backwards, it reaches the batches in it.

Record IDs and exact lot codes

Give every row an ID that never changes, such as a goods-in or despatch line number, and choose it as the Stable record ID column, so each link names that ID. Without one, TraceBench points to the row's position in the file instead, marked row numbers only.

Lot codes are compared exactly: 000017 and 17 are different codes, and so are codes that differ only in spaces or capitals. A spreadsheet can strip leading zeroes when it opens a CSV, so keep lot columns formatted as text, or export them as text from the source system.

Dates and times

Write dates one way throughout each sheet. When you load it, TraceBench asks for the written format, such as day/month/year, and the time zone, rather than guessing. A time that is missing, or that falls in a clock change and could be either of two times, stays unknown instead of being guessed. Times put the records in order on a timeline; they never create a link between records.

Following a lot across the sheets

TraceBench follows two current files at a time. Load production with despatch to follow a supplier lot forwards to every shipment, or receiving with production to follow a batch back to the lots delivered for it. A lot or batch number that appears in both files stays a possible link until you confirm one exact pair, or declare that the two columns share a numbering, with a reason.

To cover all three sheets, run one trace with each pair and download both results: each report names the files it was made from, with their fingerprints.

What TraceBench does not do

It reads CSV, not XLSX: save each sheet as CSV UTF-8 first, and formulas, lookups and other sheets in the workbook are not read. Each file can hold up to 5,000 records and 40 columns. TraceBench never changes your files, does not join rows because codes, dates or descriptions look alike, and shows quantities as written without adding, allocating or balancing them.