A modern POS will post transactions all day without complaint. What it will not do easily is sit next to METRC and tell you where the two of them disagree. That comparison is its own job, and for most stores the fastest place to do it is still a spreadsheet.

That sounds backward in 2026, since the store paid for an integration so it would not have to touch spreadsheets. But the spreadsheet isn't competing with the POS. It normalizes two exports until they share a format, lines them up, and surfaces the rows that don't match. Neither system is built to do that.

An audit engine, not a report

The mistake is building a workbook that summarizes. A summary tells you the day's sales totaled some number, when what you want is to know which three packages are off and why. That comes down to structure: a report has one tab, while this needs a tab for each source and a tab where the disagreements collect.

A workable layout is seven tabs:

  • METRC_Packages, METRC_Sales, METRC_Transfers: raw exports, untouched.
  • POS_Inventory, POS_Sales: the same, from the other side.
  • Physical_Count: what you actually counted, the tiebreaker.
  • Exceptions: the only tab a human reviews, where mismatches land with a proposed fix.

Everything flows toward that last tab. The raw tabs stay raw, so you can always trace a number back to the export it came from and you never edit a source to make a number look right. The workbook's job is to preserve what each system actually said.

The shared key

Two systems can only be compared on the fields they both carry. For a single package that's the package label, an exact-match key with no rounding and no reformatting. For a sale you need a composite, since no single field is reliable on its own. A practical sales key uses four fields:

ReceiptKey = NormalizedTimestamp | PackageLabel | Quantity | TotalAmount

Built on both sides, that key lets a lookup answer two questions: is this sale in METRC but not the POS, or in the POS but not METRC? Each answer points at a different failure, and totals alone won't tell you which one you're looking at.

Timestamps

The word "Normalized" in that key is doing more work than it looks. A spreadsheet does not store the date you see, it stores a number and shows you a format, and this is where reconciliation tends to go wrong. Two systems exporting "the same" sale time can produce strings that never match, and a malformed time field can even cause separate sales at the same instant to fold into one row on import.

So the first thing the workbook does to any timestamp is force it into one explicit shape before any lookup runs:

=TEXT(SaleDateTime, "yyyy-mm-dd hh:mm:ss")

If the timestamps aren't normalized first, the lookups will report phantom mismatches and hide real ones, and you'll spend the morning chasing differences that exist only in formatting.

The exception queue

Once the keys exist on both sides, a single column classifies each row by comparing presence and quantity:

=IFS(
  AND(MetrcQty="", PosQty<>""), "Not Found in METRC",
  AND(MetrcQty<>"", PosQty=""), "Active Package Not in POS",
  ABS(PosQty - MetrcQty) > 0.0001, "Quantity Mismatch",
  TRUE, "Match")

Everything that is not a Match drops into the Exceptions tab with its class, the two values, and a column for the physical count. That tab is the only thing a person reads. Instead of five hundred rows of sales, a reviewer reads the dozen that disagree, decides the source of truth, and records a proposed fix before anyone edits METRC or the POS. The workbook never corrects anything itself. It flags what needs a human's attention and attaches the package history so the reviewer has it in front of them.

Why not just trust the integration?

Because the integration is one of the things you are auditing. When a sync silently drops a sale or posts one twice, the POS and METRC are each internally consistent and still disagree with each other. You need a neutral third surface, one you own rather than either vendor, to see that gap. A spreadsheet is cheap and doesn't answer to anyone's release schedule.

What I learned

The workbook that worked had no macros and no formula longer than a few lines. It kept each system's export separate, forced the timestamps into one shape, and let the disagreements collect somewhere a person could look at them. That's most of it. A discrepancy has to be visible before anyone can correct it, and the spreadsheet was there to make it visible, not to fix it.