Messy source files create a reporting risk when their broken headers, mixed dates, blank references, and inconsistent amount columns leak into the final reconciliation. The mistake is to repair both files until they look alike and then treat the repaired copies as evidence. That can hide what changed and make the result hard to defend.
A clean reconciliation report does not require clean-looking inputs. It requires a controlled boundary between the original files, the comparison rules, and the report.
Keep the source exports untouched. Interpret each file separately. Match records using the strongest available evidence. Then publish the result in a new structure built around five statuses:
- Matched
- Missing
- Mismatch
- Timing
- Unresolved
That separation is how you produce a clean reconciliation report when files are already a mess. The report reflects the result of the comparison, not the layout of either source file.
Classify the mess before changing anything
Do not begin by renaming columns, deleting blank rows, or reformatting dates. First identify what kind of problem the files contain and whether it affects readability, matching, or source integrity.
| Symptom | Likely class | First check | Carry or correct |
|---|---|---|---|
| The same field has different headers | Wrong mapping | Confirm what each column represents from values and source context | Map for comparison |
| Dates use different formats or timezones | Wrong date or timing | Compare raw values, period boundaries, and posting conventions | Carry timing; correct invalid dates |
| One file has separate debit and credit columns | Wrong mapping | Confirm the sign rule and calculate one comparison amount | Map for comparison |
| References contain spaces, prefixes, or leading zeroes | Key mismatch | Compare raw and normalized references side by side | Normalize for matching |
| Row counts changed after the file was opened | Edited source file | Compare against a fresh export | Replace with untouched export |
| Transactions disappear under filters | Scope or filter error | Remove filters and confirm the exported period | Re-export or correct scope |
| The same transaction appears more than once | Duplicate record | Compare reference, date, amount, and source row | Correct or flag |
| The amount agrees but dates cross the cutoff | Timing or cutoff | Compare transaction, posting, and settlement dates | Carry to the correct period |
This table prevents two expensive errors. The first is treating every difference as bad data. A payout dated June 1 in one file and May 31 in another may be a valid timing item. The second is repairing evidence before proving whether it was damaged. If a source CSV changed after export, the safe response is a fresh export, not more editing.
Freeze the original files and prove the comparison boundary
The original exports are evidence. Preserve them exactly as received.
Create a working copy only when a spreadsheet tool requires one, and never overwrite the original. Record enough information to identify the files used:
| Control | File A | File B |
|---|---|---|
| Source | Bank export | Ledger export |
| Exported at | 2026-06-03 09:14 | 2026-06-03 09:22 |
| Period requested | May 1–31 | May 1–31 |
| Row count | 428 | 431 |
| Earliest date | 2026-05-01 | 2026-05-01 |
| Latest date | 2026-05-31 | 2026-05-31 |
| Amount basis | Signed Amount | Debit and Credit |
| Net movement | 84,219.44 | 84,184.44 |
| Blank key count | 3 | 0 |
| Duplicate key count | 0 | 2 |
These checks re-establish the baseline before matching begins. They also tell you whether the problem sits in the data or in the comparison.
If the date range is wrong, stop and re-export. If the row count changes when filters are removed, document the scope error. If the net movement changes after opening the CSV in Excel, return to the untouched export. A report cannot be cleaner than its evidence unless the evidence remains traceable.
Do not use visual neatness as an integrity test. A workbook with aligned headers can still omit rows. A raw CSV with awkward headers can still be complete.
Build a comparison layer instead of cleaning the source
You need the files to become comparable, not identical.
Create a separate mapping layer that says how each source field should be interpreted. This layer can live in a working sheet or in the comparison tool, but it must remain separate from the original exports.
| Report field | File A source | File B source | Comparison rule |
|---|---|---|---|
| Reference | Bank Reference | Transaction ID | Trim outer spaces; preserve leading zeroes |
| Date | Posting Date | Entry Date | Convert for comparison; retain raw date |
| Amount | Amount | Debit / Credit | Debit negative, credit positive |
| Description | Narrative | Memo | Display only; do not use as sole key |
| Source row | Original row number | Original row number | Preserve for audit trail |
For reference normalization, a working helper column applies the rule without touching the source:
=TRIM(SUBSTITUTE(UPPER(A2),CHAR(160)," "))Column A is the raw reference from either source file. TRIM removes leading and trailing spaces; CHAR(160) covers the non-breaking space some exports add. Keep both the original raw value and the normalized comparison value — the raw value is the audit evidence, the normalized value is the match key.
The raw value and the comparison value should both survive.
Suppose File A contains reference 0001842 and File B contains 0001842. The normalized comparison value may be 0001842, but the report should still be able to point back to the original rows. If you replace both raw values, you erase the evidence that normalization was required.
The same rule applies to dates and amounts:
- Convert
05/06/2026only after confirming whether the source uses day-month or month-day order. - Combine debit and credit columns under a documented sign rule.
- Remove currency symbols for comparison, but retain the source currency.
- Standardize case for references without changing the displayed source value.
- Treat blank references as blank. Do not invent IDs to make matching easier.
This is controlled normalization. It changes how values are compared without pretending the source files arrived clean.
Match from strongest evidence to weakest
Do not start with description text because it looks readable. Start with the field least likely to identify the wrong transaction.
Use this order:
- Unique reference plus amount.
- Unique reference alone, followed by an amount check.
- Exact amount plus exact date.
- Exact amount plus a narrow date window.
- A documented combination such as amount, account, and description.
- Manual review when no combination is strong enough.
Every match should have a reason. A clean report needs more than a status column that says "matched."
| File A row | File B row | Match basis | Result |
|---|---|---|---|
| 118 | 203 | Normalized reference + amount | Matched |
| 119 | 204 | Reference; amount differs | Mismatch |
| 120 | 211 | Amount + date within one day | Timing review |
| 121 | — | No candidate found | Missing from File B |
Duplicate amounts require extra care. Two transactions for $125.00 on the same date cannot be matched safely on amount and date alone. Add a reference, account, customer, invoice, or surrounding sequence. If the available fields still do not prove the pair, mark the item unresolved.
An unresolved row is not a failed report. It is an honest boundary around what the evidence can prove.
Separate timing items from errors
A clean report must tell the reader which differences belong in a later period and which require correction now.
Timing items are carried:
- Deposits in transit
- Outstanding payments
- Processor settlement delays
- Posted-versus-pending transactions
- Transactions recorded on one side of the cutoff
Errors are corrected or escalated:
- Missing entries
- Duplicate entries
- Wrong amounts
- Wrong dates
- Wrong accounts
- Corrupted references
- Rows omitted by a filter or incomplete export
Do not place all of these under "unmatched." That label forces the reviewer to investigate the same rows again.
| Finding | Evidence | Client-safe explanation | Action |
|---|---|---|---|
| Deposit crosses month end | Same reference and amount; ledger date May 31, bank date June 1 | The deposit was recorded in May and cleared the bank in June. It is a timing item. | Carry and verify next period |
| Fee missing from ledger | Bank row has no ledger candidate; amount and date confirmed | A bank fee was not recorded in the ledger. | Post the fee |
| Payment duplicated | Two ledger rows share the same reference and amount; bank has one row | The ledger contains a duplicate payment entry. | Reverse the duplicate |
| Settlement amount differs | Reference agrees; processor amount is $35 higher | The settlement is linked, but a $35 deduction is not explained by the available file. | Obtain fee or adjustment detail |
The client-safe explanation column turns the technical comparison into a report someone else can use. It states the finding without blaming the client, the file, or the person who prepared it.
Build the report around decisions, not source columns
The final report should not be a merged dump of every source column. Give each section a job.
1. Scope and control summary
State the files, account, period, export dates, row counts, and totals compared.
2. Reconciliation summary
| Status | Count | Amount | Meaning |
|---|---|---|---|
| Matched | 412 | 96,430.18 | Records agree under the stated matching basis |
| Timing | 4 | 2,180.00 | Valid items expected to clear in another period |
| Missing | 2 | -285.74 | Records present in one file only |
| Mismatch | 1 | 35.00 difference | Related records with conflicting values |
| Unresolved | 1 | 125.00 | Available evidence does not prove a match |
3. Exception detail
Include the raw references, dates, amounts, source row numbers, match basis, status, explanation, owner, and next action. The exception list is where detail belongs.
4. Corrections and carry-forward items
Separate entries corrected in the current period from timing items that remain open. Include who owns each unresolved action and when it should be checked again.
5. Completion statement
State whether the reconciliation is fully resolved, resolved subject to timing items, or still open. Do not say "reconciled" when an unexplained amount remains.
This structure also gives you the evidence needed to explain a reconciliation discrepancy to a client without sending the client your working spreadsheet.
What not to include in the clean report
More columns do not create more confidence.
Remove working-only material from the published report:
- Failed lookup formulas
- Temporary helper columns with no documented meaning
- Duplicate copies of the same source field
- Color-only status coding
- Notes such as "check this" with no owner or action
- Filtered-out exceptions
- Rows changed to force a zero balance
Keep enough evidence to reproduce the finding. Remove the debris from producing it.
A reviewer should be able to answer four questions from the report:
- What exactly was compared?
- Why was each record classified that way?
- Which differences were corrected or carried?
- What remains open, and who owns it?
If the report cannot answer those questions, it is still a working file. Generic diff output has the same weakness: it shows that cells differ but does not explain the financial status or required action. That is why spreadsheet comparison alternatives for clean audit reports need structured exceptions rather than a raw line-by-line diff.
Run the final integrity checks
Before handing over the report, tie it back to both sources.
Confirm that:
- Every source row is matched, classified as an exception, or explicitly excluded with a reason.
- No source row was matched more than once unless a documented split or grouping requires it.
- Matched totals plus exception totals explain the movement in each file.
- Timing items have a target period for follow-up.
- Corrections are separate from proposed corrections.
- Unresolved items are not hidden inside a general mismatch total.
- Source row references still point to the untouched files.
- The summary agrees with the exception detail.
Then read the report as the recipient will read it. The first page or first section should give the scope, result, and remaining risk. The row detail should support that answer, not delay it.
The source files can remain inconsistent. The output cannot. Preserve the raw evidence, document the mapping, classify every row, and publish only the scope, statuses, exceptions, actions, and proof needed to defend the result.
