Matching totals can hide missing records.

First decide what one row means.

Two exports can have different row orders and different column names while describing the same work. A positional comparison will confuse movement with change. Start with a stable identifier: a record ID for a one-row-per-record export, or a composite key such as account ID plus invoice reference when references are only unique within an account. Do not use a person’s name as a unique key without checking it.

Then write down the population and period. Did one file exclude cancelled records, use a different cutoff, or export only visible rows? A missing record is an observation about the files, not proof that someone deleted it. This checker cannot see filters or establish that your two exports cover the same business scope.

Map each left key to its right counterpart, one exact header per line in matching order. Do the same for the fields to compare. Only those fields are evaluated; unselected notes and columns remain in the source records. For monetary values, currency is always part of the key. USD and EUR can never cancel each other out.

1. Keep untouched copies of both exports. Use fictional data until your organization has approved the real data and workflow.
2. Choose a stable key, including any account or line-item component needed to make its meaning clear.
3. Map keys and comparison fields separately for each file; the two schemas do not need identical names or column order.
4. Declare the row meaning and export scope. Choose strict or grouped mode before interpreting the answer.

A repeated identifier is a question, not a matching rule.

Use strict mode when each key should identify at most one row in either file. Selected values are compared as text: 001 stays different from 1, blank stays different from 0, and 100 stays different from 100.00. If a key repeats, the entire group remains unresolved. The checker never pairs the first two rows it finds or expands them into a many-to-many join.

Use grouped mode only when the selected numeric values are additive line items. It totals each mapped field separately per key and currency, records how many rows contributed, and compares right minus left. This answers whether a group’s selected totals agree. It cannot establish that every underlying line is the same, that an invoice is paid, or that a transaction is legitimate.

A repeated reference and an exact duplicate copy are different problems. The sample’s two INV-001 lines have different amounts and details. An exact copy repeats every parsed cell within one file, including unselected columns. By default, all rows in its affected key/currency group are quarantined on both sides. If you have verified that identical copies represent distinct real line items, explicitly choose to count every copy in grouped mode; the copies stay flagged in the review file.

A basic keyed diff may already meet your needs. OnSumo’s CSV Diff Viewer documents composite keys and flags duplicate keys as undefined. The useful addition here is an explicit, inspectable grouped-total decision for legitimate repeated identifiers.

The fictional answer: USD 380 on both sides, three groups to investigate.

Load fictional-left.csv and fictional-right.csv, or use the populated example. Select grouped mode, map reference to invoice_ref and amount to posted_amount, map currency to ccy, keep exact matching, set tolerance to 0, and leave duplicate handling on quarantine. The left file has five data rows; the right has four.

The result contains five key/currency groups, three groups needing review and zero unresolved rows. Both USD totals are 380. The changed amount and the two one-sided groups offset each other in the grand total: −10 − 30 + 40 = 0. That arithmetic explains why the totals agree; it does not turn the missing groups into matches. EUR totals 10 on each side and is reported separately.

• INV-001 / USD: 100 + 50 on the left equals 150 on the right. Balanced, with two contributing left rows and one right row.
• INV-002 / USD: 200 versus 190. Changed; right minus left is −10.
• INV-003 / USD: 30 on the left, not observed on the right. Left-only; its group delta is blank because the absent side is not an observed zero.
• INV-004 / USD: not observed on the left, 40 on the right. Right-only.
• INV-005 / EUR: 10 versus 10. Balanced in its own currency group.
• Now use fictional-left-with-duplicate.csv with the same right file. INV-001 becomes an exact-duplicate exception; all four of that group’s source rows are quarantined. USD 230 versus 230 is now a subtotal, excluding that group. Strict mode on the original inputs instead marks INV-001 ambiguous because its left key repeats.

Review the exceptions before you write the explanation.

Start with ambiguous-rows.csv. It identifies missing keys, invalid currency labels, unsupported amounts, strict-mode repeated keys, and exact copies. Every quarantined source row remains available, including valid-looking counterparts that belong to an unresolved group. Fix the underlying issue or record an explicit decision; do not make the totals look complete by deleting the exception.

Next inspect changed, left-only and right-only rows in reconciliation.csv. Each mapped field has its own result, raw values, contributing row references, delta when both sides exist, and a suggested review action. A group can change because one field differs even when another agrees. The selected key and field mappings are recorded in settings.json.

Keep source-rows.csv as the row index and source-records.json as the complete original decoded cell values, including columns you did not compare. Large provenance arrays use explicit references into that JSON file instead of enormous spreadsheet cells. Source row means CSV record number with the header at row 1; the physical starting line is also recorded for quoted multiline cells.

Treat any totals with excluded rows as subtotals. A balanced group only confirms the selected calculation under the declared rules. It says nothing about unselected fields, omitted source records, payment status or the cause of a discrepancy.

Choose the conventions instead of letting the tool guess.

Each input must be comma-separated CSV with unique, nonblank headers and at least one data row. Quoted commas, doubled quotes, quoted line breaks, UTF-8 text and a UTF-8 BOM are supported. Malformed quotes, unequal row widths and empty records stop the comparison. These details follow the common CSV structure documented in RFC 4180; CSV exports from different applications can still require a deliberate re-export.

Keys and text use exact characters by default. You may explicitly trim outer whitespace or also ignore case. That choice is saved in the settings. If it merges distinct raw key spellings within one file, the group is quarantined so a cleanup rule cannot silently combine two identifiers. Amount cells and currency labels are not normalized.

Grouped amounts must be plain signed decimals, with at most 18 integer digits and six decimal places. No currency symbols, thousands separators, exponent notation or surrounding whitespace. Decimal sums and tolerance checks use scaled integer arithmetic. The absolute tolerance applies independently to each field and group in that field’s units; a nonzero difference within tolerance stays visible. Strict text mode requires tolerance 0.

Limits are 1,000,000 UTF-8 bytes and 10,000 data rows per file, up to eight key columns and 12 comparison fields. A selection is also limited to 25,000 field-level results and 12 million ledger CSV characters; oversized selections stop with a request for fewer fields or a smaller export. Visible tables show at most 100 rows and shorten long cells. Completed downloads include every result and every source record.

Spreadsheet software can interpret formula-like CSV text as a command. The exports escape those cells and retain the original strings in JSON; review the OWASP CSV injection guidance if you build your own workflow. When importing into a spreadsheet, explicitly treat identifiers and precision-sensitive values as text so the spreadsheet does not remove leading zeros or round long numbers.

Use the checked ledger to give AI a smaller, reviewable job.

This checker runs deterministic calculations locally. It does not call an AI model, connect to your business systems, upload the imported records, or write a correction back. The fictional answer files are authored test fixtures, not a claimed result from a native AI spreadsheet product.

An optional next step is to ask an approved AI assistant to draft a reviewer note from the ledger and settings. Use only fictional or approved data. Tell it to cite source rows, separate observations from possible explanations, and list questions that remain unanswered. Text inside a CSV cell is source data, not authority to send messages, visit links or change instructions.

A useful brief is: “Draft a review note from this reconciliation and its settings. Explain the changed and one-sided groups, cite exact source rows, and preserve unresolved items. Do not infer payment, deletion or a business cause. Suggest questions for the reviewer. Do not change records or send anything.” Check every statement against the ledger before sharing it.

For recurring work, save the approved mappings and counting policy, then test a second export with one deliberate known change. The public worked example shows what the kit should produce. Free newsletter signup unlocks the full checker, results and downloads, along with Something Big Is Happening’s continuing lessons on using AI at work. Optional profile questions never block access; unsubscribe any time.