Comparing Two QuickBooks Company Files to Find Where They Diverge
Two QuickBooks Desktop files that should match do not. Here is the comparison method our engineers use to find the account where the divergence starts.
Two copies of a QuickBooks Desktop company file are supposed to hold the same numbers. Often they do not. A restored backup sits next to the working file, two people posted to different machines for a week, or a file came back from a conversion with a moved total.
Comparing every transaction in both files by eye finds nothing useful. The method that works is narrower: fix a baseline date, compare the balance sheet on that date account by account, and let the report differences point to the transactions behind them. Our engineers follow the same sequence when two files arrive together.
Prepare copies and agree on the settings
Protect the file you trust first. Create a local backup or a portable copy and leave it unopened. If the file sits on a network share, switch to single-user mode for the reports. A comparison reads live data, and a colleague entering invoices mid-comparison changes the result under you.
Then make both files report the same way: accrual basis, same date, same fiscal year start. A cash basis report leaves payments sitting in undeposited funds out of the picture, and half your differences will belong to the report, not the data.
A cutoff date before the recent activity gives a clean baseline
The cutoff date is the heart of the method. Comparing both files as of today mixes real divergence with noise: payments not yet deposited, invoices half edited, a deposit made this morning in one copy only. Choose a date when the files should have agreed. The end of the last fully reconciled month is the usual choice. If nobody can vouch for that month, step back one more.
- Open the first file and run Reports > Company & Financial > Balance Sheet Standard, with the As Of date set to your cutoff.
- Export the report to Excel, or write down each account and balance.
- Repeat in the second file, same date, same basis.
- Paste the two columns side by side and add a difference column.
- Note which accounts differ, and by how much.
If every account matches at the cutoff, the divergence came later and you can work forward. If accounts already differ, move the cutoff back a month and compare again. Each step backward is cheap. Do not try to interpret a noisy comparison.
Compare receivables first
Receivables usually show the split earliest. Run the A/R Aging Summary as of the cutoff in both files. Compare the total, then the aging columns, then customer by customer. The aging report beats the balance sheet here because it names the customer the moment a figure disagrees.
When a single customer differs, run a QuickReport on that customer across the weeks around the cutoff in both files. The transaction that exists in one file and not the other, or the payment applied to a different invoice, is normally visible within a page.
Inventory separates quantity from value
Inventory can agree in dollars and disagree in units, or the reverse. Run the Inventory Valuation Summary as of the cutoff in both files and compare item by item. A quantity that matches while the value does not points at cost: an average cost that rebuilt differently, an adjustment posted to one copy, or a bill entered in one file only.
Where an item disagrees, open its Inventory Valuation Detail and look for stretches where quantity on hand ran negative. Negative inventory forces QuickBooks to guess at cost, and two copies can carry different guesses long after the quantities even out.
Undeposited funds hides money in transit
This account is missed most often, because a balance there looks like nothing happened. Payments received into undeposited funds in one copy and deposited in the other produce differences in the bank account, in receivables, or in both.
Open the chart of accounts in each file, right-click Undeposited Funds, and run a QuickReport as of the cutoff. A balance in one file and zero in the other means a deposit was recorded in one copy only. The date of that entry is the date the files diverged.
Walk forward from the cutoff
Once the cutoff balances agree and you have dated the first disagreement, repeat the comparison a week or a month at a time until a new difference appears. Then open Modify Report and add the Entered/Last Modified filter. It catches backdated entries: a transaction posted before the cutoff but typed in after it will never show in a straight comparison of the cutoff date.
When the files never agree at any cutoff
Some pairs of files produce no matching cutoff at all. One file may carry damage the reports do not surface, or a change may sit so far back that no trusted baseline exists. Run File > Utilities > Verify Data on both files and treat any error as a finding in its own right. A file that fails Verify or Rebuild can disagree with a healthy copy in ways no comparison explains, and more comparing will not fix it.
Comparison also cannot undo what it finds. It cannot recover a transaction deleted without trace, and it cannot combine two sets of books that need merging diverged histories rather than correcting one of them. That work takes more engineering than the comparison that located the split.
Signs the comparison worked
- The difference column on the balance sheet comparison reads zero on every account at the cutoff.
- The list of differing accounts shrinks from many, to one or two, to a single customer or item.
- You can name a transaction that exists in one file and not the other, with its date and amount.
- After the correction is posted in both files, today's totals match as well as the cutoff totals.
When that list describes your session, the two files are telling the same story again, and any remaining difference is a transaction, not a mystery.