Compare two lists with AI and Excel: find the difference in a reconciliation

The job: two lists that should agree

Most reconciliations come down to comparing two lists: the bank statement and the cash ledger, payment processor payouts and recorded sales, a vendor statement and accounts payable, an inventory count and the system. The work is the same each time. Match what agrees, find what doesn't, and explain every difference.

The examples below use a sheet called Bank and a sheet called GL. Both have the date in column A, the description in column B and the amount in column C, with money out as negative numbers on both.

Step 1: Find the exact matches with formulas

Formulas are better than AI here. They're instant, they don't skip lines, and anyone can check them.

In column D of the Bank sheet, count the GL lines with the same amount and the same date:

Don't stop at "found". A count of 1 doesn't prove a one-to-one match: two bank lines for $500 on the same day will both find the same GL line. Compare how often each amount appears on each side. On the Bank sheet:

If figures came from different systems, round them first. An amount calculated in one system can carry stray decimals that stop it matching, so compare =ROUND(C2,2) rather than C2.

Step 2: Look for the near matches

What's left after the exact matches is where the work is. The common near misses:

• Dates a few days apart. A check is recorded when it's written and clears days later. Widen the date window: =COUNTIFS(GL!C:C,C2,GL!A:A,">="&A2-5,GL!A:A,"<="&A2+5).
• Different descriptions for the same payee, such as an abbreviated name on the bank and the full name in the ledger.
• One-to-many. A single deposit covering three invoices, or one payment split across two bank lines.
• Swapped or transposed amounts. If the total difference divides evenly by 9, look for two digits swapped in one figure.
• Sign errors. A refund recorded as a payment shows up as a difference of twice the amount.

To see the likely ledger line next to each bank line, XLOOKUP can bring back its description:

Step 3: Let AI propose matches for the remainder

On a long, messy list, AI is good at proposing the near matches a formula can't see. Give it only the unmatched lines from each side, and ask it to sort them into groups you can check.

Attached are two lists of unmatched items: Bank (date, description, amount) and GL (date, description, amount). Money out is negative in both.

Sort every line from both lists into one of these groups:
1. Likely match: give the bank line, the GL line or lines, and the reason (date a few days apart, payee spelled differently, one payment covering several entries).
2. Possible match: as above, with what makes you unsure.
3. No match: list the line and say which side it's on.

Don't force a match to make the totals agree. Every line must appear exactly once. At the end, give the total of each group for each list.

Then check it. Count the lines: every line from each list should appear exactly once. For each likely match, read both lines yourself. For one-to-many matches, add up the lines: the amounts should agree to the cent.

Accountants have described AI saving hours on large reconciliations while still needing a few sums corrected. That's the right expectation: a fast first pass that you verify.

Step 4: Prove the difference

Whatever is left unmatched has to explain the gap between the two balances. For a bank reconciliation, the classic layout adjusts each side to the same figure:

The two adjusted balances agree, so every difference is explained. If they don't agree, there's still an unexplained item, however small. A difference of a few cents is still a difference: it's often the clue to a misread or rounded figure.

Items on the books side need entries, such as the bank fee above. Items on the bank side, such as outstanding checks, should clear next month. If they don't, follow them up.

The same method for other reconciliations

The steps don't change from one reconciliation to another. The usual reasons for a difference do:

• Payment processor payouts vs recorded sales: fees netted from the payout, refunds and chargebacks, and payouts that cover several days of sales.
• Vendor statement vs accounts payable: invoices the vendor has sent that you haven't received or recorded, payments in transit, and credits taken on one side only.
• Intercompany balances: timing differences at the cutoff, currency translation, and charges booked by one entity but not yet by the other.
• Credit card statement vs the card clearing account: transactions posted after the statement date, and personal or disputed charges still to be resolved.

Knowing the usual causes helps you judge the AI's proposed explanations. A suggestion that fits one of them is worth checking first. One that fits none of them needs real evidence before you accept it.

Record what you matched

Keep the working: the formulas, the AI's proposed matches with your decision on each, and the reconciling items with their support. Note which matches came from AI and that you checked them. A reviewer should be able to follow any line from one list to the other.