How to reconcile two files: a step-by-step guide
A practical walkthrough of the reconciliation workflow
Reconciliation is the process of comparing two versions of the same data set - a PRE (source, "before") file and a POST (target, "after") file - to prove they agree and to surface every record that was added, removed or changed. It is a daily reality in finance, accounting, audit and data migration, where a single missed difference can mean a misstated balance or a broken report.
This guide explains the workflow in plain terms. It applies whether you reconcile in a
spreadsheet, a script or a dedicated tool like MetiRecon. If you have been
doing this with a wall of VLOOKUP formulas, the steps below will feel familiar,
just far less error-prone.
1. Load both files
Start by opening the two files you want to compare. Real-world data arrives in many shapes, so the first job is to read them correctly:
- Format: CSV, TSV, Excel (XLSX), JSON, XML, fixed-width and FIX are the common formats from ledgers, ERPs and databases.
- Delimiter: commas, semicolons or tabs - European exports often use a semicolon, which trips up naive imports.
- Encoding: UTF-8 is standard, but legacy systems still emit Windows-1250, Latin-1 and others. The wrong encoding turns accented names into garbage and breaks keys.
- Header row: confirm the column names line up with the actual data.
Getting this right up front prevents the most common cause of "phantom" differences later.
2. Choose your keys
A key is the column (or combination of columns) that uniquely identifies a record so it can be matched across both files - an account number, transaction ID, ISIN, invoice number or a composite like date + account + reference.
Good keys are stable and unique. If a single column is not unique (for example, the same account appears on many rows), combine several columns into a composite key. Choosing the key well is the most important decision in the whole process: match on the wrong field and every row looks "added" and "deleted" at once.
3. Map the columns to compare
Source and target files rarely have identical layouts. One might call a column
Amount, the other value_net; the order may differ; extra columns may
exist on one side only. Column mapping connects the fields that represent the
same thing and lets you pick which values are actually compared for differences and which are
carried along for context.
4. Set matching rules and tolerances
Not every difference is meaningful. Without rules, trivial formatting noise drowns out the real changes. Configure:
- Numeric tolerance: treat values within, say, 0.01 as equal to absorb rounding between systems.
- Case-insensitive matching: so
PLNandplnare not reported as different. - Whitespace handling: trim leading and trailing spaces that sneak in from exports.
The goal is simple: flag the differences that matter and stay silent about the ones that do not.
5. Run the comparison and review the differences
With keys, mapping and rules in place, run the reconciliation. Every row should fall into one of four buckets:
- Identical - present in both files and equal within tolerance.
- Modified - same key, but one or more compared values differ.
- Added - present only in the POST file.
- Deleted - present only in the PRE file.
Reviewing these four groups separately is what makes reconciliation trustworthy: nothing is averaged away and nothing slips through unseen.
6. Export a clean report
Finally, export the result as a PDF, Excel or CSV report you can attach to a close package, hand to an auditor or send to a client. A reusable configuration means the next month's reconciliation is a single click, not a rebuild.
Common pitfalls to avoid
- Non-unique keys: the number-one cause of nonsense results.
- Wrong encoding or delimiter: silently corrupts keys and values.
- No tolerance on numbers: rounding differences flood the report.
- Manual copy-paste: spreadsheets scale poorly past a few thousand rows and invite formula errors.
Do it in minutes with MetiRecon
MetiRecon runs this whole workflow on your desktop. It handles formats and encodings, you set the keys and tolerances, then it exports the report. Everything stays 100% offline, so sensitive financial files never leave your machine. See the common use cases, compare it with reconciling in Excel or read the FAQ.