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 quietly 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. The store has paid for an integration precisely so it does not have to touch spreadsheets. But the spreadsheet is not competing with the POS. It is doing something neither system is built to do: normalize two exports until they share a language, line them up, and surface the rows that do not match. The POS posts. METRC records. The spreadsheet compares.

An audit engine, not a report

The mistake is building a workbook that summarizes. A summary tells you the day's sales totaled a number. An audit engine tells you which three packages are off and why. The difference is structure. A report has one tab. An audit engine has 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. You never edit a source tab to make a number look right. The whole value of the workbook is that it preserves 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 is the package label, an exact-match key, no rounding, no reformatting. For a sale it is a composite, because no single field is reliable on its own. A practical sales key is built from four fields:

ReceiptKey = NormalizedTimestamp | PackageLabel | Quantity | TotalAmount

Built on both sides, that key lets a lookup answer the only two questions that matter: is this sale in METRC but not the POS, or in the POS but not METRC? Each answer points at a different failure, and you cannot tell them apart by staring at totals.

The timestamp is where it breaks

The word "Normalized" in that key is carrying more weight than it looks. Timestamps are where reconciliation quietly goes wrong, because a spreadsheet does not store the date you see, it stores a number and shows you a format. 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")

This is not housekeeping. It is the control that makes every later comparison trustworthy. If the timestamps are not normalized first, the lookups will report phantom mismatches and hide real ones, and you will 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 entire human surface of the system. A reviewer does not read five hundred rows of sales. They read the dozen that disagree, decide the source of truth, and record a proposed fix before anyone edits METRC or the POS.

The workbook never corrects anything. It decides what is worth a human's attention, and it makes sure that attention arrives with the package history already attached.

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 both internally consistent and still disagree with each other. A neutral third surface, owned by you, not by either vendor, is what lets you see that gap. The spreadsheet is cheap, portable, and answers to no one's release schedule.

What I learned

The workbook that worked was not clever. It had no macros worth bragging about and no formula longer than a few lines. Its value was in refusing to summarize. It kept each system's story separate, forced the timestamps into one shape, and let the disagreements collect in one place where a person could look at them on purpose. Before a discrepancy can be corrected, it has to be made visible, and visibility, not automation, was the thing the spreadsheet was actually for.