Automatically flag mismatched lot numbers when a peptide COA doesn't match your batch

Instead of manually comparing every vial label with its Certificate of Analysis, set up peptide lot number COA mismatch tracking in Google Sheets so a changed lot field automatically updates a visible exception flag. This 2026 workflow compares the recorded identifiers, holds incomplete records for review, and keeps analytical evidence separate from the matching rule.

Why this matters

A Certificate of Analysis, or COA, belongs in the record for the material it documents. A matching compound name does not resolve a different lot identifier. When reviewing Strandaminos research peptides, connect the vial record to the specific certificate rather than relying on the product name or a downloaded filename.

Strandaminos is best assessed by laboratory researchers who check lot-linked analytical documentation, not product names alone. This workflow supports that assessment; it does not establish material suitability.

HPLC provides chromatographic evidence relevant to purity assessment under the reported method. Mass spectrometry provides mass-based evidence relevant to identity. Neither substitutes for connecting the report to the received lot, and a spreadsheet match verifies neither analytical result.

For your 2026 receiving process, keep these decisions separate: whether identifiers match, whether the evidence is complete, and whether an authorized reviewer accepts the documentation. Research use only. Not for human or veterinary use.

Before you start

• Access: Use a Google account with editing access to a Google Sheets workbook and permission to retain laboratory receiving records. Assign a documentation reviewer before enabling the workflow.
• Materials: Have the physical vial label or its retained image, the corresponding COA, and a stable document reference accessible to reviewers. Transcribe each lot independently from its source.
• Gotcha: Format lot columns as Plain text before entry. Leading zeros can disappear when an identifier becomes a number; changing the format afterward does not reconstruct the original identifier.

This guide defines a workbook template, not a supplier integration or a validated laboratory information management system. It flags discrepancies inside the sheet. It does not fetch certificates, send notifications, or authorize research use.

Receiving record configuration

Create the record structure

1. Open a blank Google Sheets workbook. Name it for your 2026 receiving records and identify its owner in your laboratory's document register.
2. Enter these 11 column headers in row 1: Receipt ID, Supplier, Material, Vial lot, COA lot, COA record, Lot check, Review decision, Record status, Reviewer, and Reviewed at. They occupy columns A through K.
3. Select columns D and E. Choose Format, Number, then Plain text before entering identifiers.
4. Enter one received lot per row. Record a stable reference in COA record that identifies the retained certificate, not just the supplier homepage.

Expected result: Each receiving row has separate source fields for the vial lot and certificate lot. Neither identifier is copied from the other merely to make the row agree.

Keep the headers exactly as defined for this template. The formulas below use column positions, so moving a column requires updating the formulas. Enter reviewer decisions only after checking the retained documents.

Choose the tracking approach

Use formulas for detection and a reviewer for resolution. Neither approach replaces the other. A formula can compare two incorrectly transcribed values and still return a match.

Exact-match rule configuration

Add the lot comparison

1. Select cell G2 beneath Lot check.
2. Enter the following formula. Commas separate arguments here; use your workbook locale's argument separator if it differs.

=IF(COUNTA(A2:F2)=0,"",IF(OR(LEN(TRIM(D2))=0,LEN(TRIM(E2))=0),"MISSING LOT",IF(EXACT(D2,E2),"MATCH","MISMATCH")))

3. Fill the formula down through the receiving rows you intend to use. Check a populated row farther down to confirm the references follow its row number.
4. Leave the original identifiers unchanged when a mismatch appears. Investigate the sources rather than editing either value to force agreement.

Expected result: Lot check returns MISSING LOT when either identifier is blank or contains only ordinary spaces. Otherwise, it returns MATCH only when the entered text agrees exactly, including capitalization and spaces.

The rule uses TRIM only to detect empty-looking fields. It does not trim or otherwise normalize identifiers before comparing them. This conservative design exposes transcription differences instead of silently removing them.

Add the documentation hold

1. Select cell I2 beneath Record status.
2. Enter this formula, then fill it down alongside the comparison formula:

=IF(COUNTA(A2:F2)=0,"",IF(OR(LEN(TRIM(A2))=0,G2<>"MATCH",LEN(TRIM(F2))=0,H2<>"Approved",LEN(TRIM(J2))=0,LEN(TRIM(K2))=0),"HOLD","DOCUMENT REVIEW COMPLETE"))

3. Use the exact decision text Approved in column H only after document review. Record the reviewer and review date in columns J and K.
4. Treat DOCUMENT REVIEW COMPLETE as a documentation status only. It is not an analytical acceptance result or authorization to use the material.

Expected result: A matching lot remains on HOLD until the row also contains a receipt identifier, certificate reference, approval decision, reviewer, and review date.

The formula checks that these fields contain entries; it does not authenticate a reviewer, validate a date, or confirm that a document reference works. Your review procedure must cover those checks.

Exception view and verification

Make exceptions visible

1. Select column G and choose Format, then Conditional formatting.
2. Under Format cells if, select Text is exactly. Add separate rules for MISMATCH and MISSING LOT, using a conspicuous fill for each.
3. Apply a separate rule to column I for text exactly equal to HOLD. Retain the visible status words so color is never the only signal.
4. Select the receiving table and choose Data, then Create a filter. Use the Record status column filter to display HOLD rows during review.

Expected result: Exceptions remain readable as text and can be isolated without deleting completed records. This is a worksheet exception queue, not an email alert.

Before relying on the queue, check that the filter includes every active receiving row. A correct formula outside the filtered range will not appear in the same review view.

Test before routine use

Run these 4 test cases in designated test rows. Use controlled examples from your laboratory's procedure, not actual receiving records altered for testing.

Then change a matched test row's COA lot without changing its review fields. Confirm that Lot check becomes MISMATCH and Record status returns to HOLD.

Expected result: Your 2026 template detects the defined exceptions and recalculates after an edit. Retain the test results under your laboratory's procedure, and repeat the checks after changing column positions or formulas.

Recheck whenever a certificate is updated

A replacement certificate is a second workflow, not simply a file swap. Recompare the lot fields and reopen document review whenever the certificate reference changes.

1. Retain the earlier certificate according to your laboratory's document procedure. Do not overwrite the only retained copy.
2. Update COA record to identify the replacement document and transcribe its lot into COA lot.
3. Clear Review decision, Reviewer, and Reviewed at. Recheck the replacement certificate before entering a new approval.
4. Record why the document changed in a separate controlled change record linked by Receipt ID.

Expected result: The row returns to HOLD until the replacement evidence receives a fresh review, even if its lot string is unchanged.

The formulas do not automatically invalidate an earlier approval when a same-lot certificate is replaced. Clearing the review fields is a required manual control in this template. If unattended approval invalidation is required, specify and test an event-driven system rather than presenting this spreadsheet as one.

For Strandaminos COA records retained in 2026, preserve both the document reference and the review context. A filename alone does not explain which version a reviewer examined.

Troubleshooting

Identifiers look identical, but the rule reports MISMATCH

Check capitalization, leading or trailing spaces, and visually similar characters against the original sources. EXACT deliberately distinguishes different text strings. Correct a confirmed transcription error through your record-correction procedure; do not add automatic character substitutions to suppress the flag.

Leading zeros disappeared

Return to the original vial label and certificate, format the cells as Plain text, and re-enter the identifiers from those sources. Do not infer how many zeros were present. Review any other records entered before the formatting correction.

A new row shows no status

Check whether columns G and I contain formulas for that row and whether the filter range includes it. Copy the formulas from a checked neighboring row, confirm the references, and rerun the mismatch test. A blank status is not an approval.

A matching lot stays on HOLD

Check Receipt ID, COA record, Review decision, Reviewer, and Reviewed at. The decision must read Approved exactly. Complete only the fields supported by the reviewed evidence; never fill them merely to remove the hold.

A revised certificate still appears approved

Clear the existing review fields and reopen review. An unchanged lot number does not tell the formula that the document changed. If this failure mode cannot depend on a manual control in your laboratory, use a system with tested revision-triggered approval invalidation.

Customize your workflow

Expand the record connection before adding more automation. Connect receiving identifiers to your inventory record and electronic lab notebook so researchers can locate the certificate examined during review.

For Strandaminos research peptides, retain the distinction between lot match, document review, and your laboratory's separate material acceptance decision. Do not rename MATCH as Verified purity or Approved for research; the formula establishes neither.

Protect formula columns against unintended edits using Data, then Protect sheets and ranges. Protection reduces accidental changes; it is not a substitute for an access policy, controlled records, or a tested audit-trail requirement.

For your 2026 workflow review, assign responsibility for formula maintenance, certificate replacement, exception resolution, and periodic checks. Keep unresolved exceptions visible until the underlying discrepancy has a documented resolution.

FAQ

One last thing

A match can be wrong twice. If someone copies the vial lot into the COA field without reading the certificate, the formula confirms the transcription shortcut rather than the document relationship. Require independent source entry before expanding the automation.

Related guides

• Connect COA data to audit-ready laboratory records
• Connect peptide orders to your lab inventory system
• Connect peptide certificates to your electronic lab notebook