How to reconcile two systems by hand: a repeatable method
Reconciliation has a reputation as tedious detective work. The detective part is real; the tedious part is mostly because no one ever showed you the method. There is one — the same method every time, whether you're matching orders to payments or inventory to a warehouse feed. Here it is, written so you can run it by hand and always know exactly where you are.
What does it mean to reconcile two systems?
To reconcile is to prove that two independent records of the same thing agree — and, where they do not, to explain why. The output is not just match or no match. It is a classified list: rows that agree, rows that exist on one side only, and rows that exist on both but disagree on a value. Each difference has a reason and a next action.
The five steps
1. Choose the primary ID
Decide the field, or combination of fields, that identifies the same record on both sides — its primary ID. This is the single most important decision; a wrong key makes every later step meaningless.
2. Normalize both sides
Bring both datasets to the same shape before comparing. Trim whitespace, fix casing, strip prefixes, standardize dates to one format, convert amounts to one unit. Do this to the key first, then to the values you intend to compare.
3. Match on the key
Join the two sides on the primary ID. Every row now falls into one of three buckets: matched on both sides, present only in A, present only in B. The one-sided rows are your first findings — something was created in one system and never made it to the other.
4. Compare the matched pairs
For rows that matched, compare the fields that matter — amount, quantity, status, date. Equal values confirm the match. Unequal values are mismatches, and you record both values so the difference is visible, not just flagged.
5. Classify and decide
Sort every difference by cause and assign an action. The classification is what turns a pile of discrepancies into a closeable list.
| Category | Meaning | Typical action |
|---|---|---|
| Missing in B | Exists in A only | Investigate why it never synced; create or void |
| Missing in A | Exists in B only | Same, mirrored |
| Value mismatch | Matched but a field differs | Correct the wrong side |
| Timing difference | Same event, different period | Accept; expect it to clear next period |
| Accepted variance | Known, tolerated difference | Document and move on |
Why keep the evidence?
The valuable artifact of reconciliation is not the final balanced state, it is the trail: which rows matched, which rule classified each difference, and what the two values were. That trail is what lets someone else trust the result without redoing the work — and what lets you answer why three months later.
When to stop doing this by hand
The manual method is the right way to learn reconciliation and the right tool for a one-off. It stops scaling when the same comparison runs every week, when datasets grow past what a spreadsheet handles comfortably, or when more than one person needs to trust the result. At that point you want the steps preserved as a repeatable, auditable process rather than a workbook someone rebuilds each month.
Frequently asked questions
What is the first step in reconciling two data sources?
Choosing the primary ID — the field or fields that identify the same record on both sides. Every later step depends on it, so validate that the key is unique and present on both sides before comparing any values.
What are the three outcomes of matching two datasets?
After joining on the key, every row is either matched on both sides, present only in the first source, or present only in the second. Matched rows are then compared field by field to find value mismatches.
How do you classify reconciliation differences?
Group each difference by cause: missing on one side, value mismatch, timing difference, or accepted variance. Each category maps to a clear next action, which is what makes the result closeable.