Reconcile two Excel lists with unmatched-record reports

Comparing two lists requires checking both directions. A lookup from list A into list B can identify A records missing from B, but it does not reveal records that exist only in B. Reconciliation should classify matched keys, mismatched values and one-sided records separately.

Before comparing, confirm that each list has one row per intended business key and that amounts use the same unit, currency and reporting cutoff. Otherwise a lookup result can be technically correct but commercially misleading.

Create a small example with equal totals

Enter two Excel tables named ListA and ListB, each with Key and Amount columns:

Both lists total 600. At the record level, only K1 agrees. K2 differs, K3 exists only in A and K4 exists only in B. This demonstrates why comparing grand totals is insufficient.

The same original fixture is executable in the SQL reconciliation lesson, where its four expected result rows are checked against SQLite output.

Check key uniqueness first

Add a count helper in each table, such as =COUNTIF(ListA[Key],[@Key]). Every key should occur once under this example's contract. If it does not, resolve the grain before comparing amounts.

A duplicate reference key can make XLOOKUP select an arbitrary first business value. A Power Query merge can instead expand one source row into several matches. Those tools expose different symptoms of the same unresolved relationship.

For invoice-line lists, use the full line key. If the question is invoice-level, summarize each source independently to one invoice row before reconciliation, retaining detail for later investigation.

Produce unmatched lists in both directions

In Power Query, merge A as the left table with B on Key and select Left Anti to obtain K3. Repeat with B as the left table and A as the right to obtain K4. Label the outputs A_only and B_only so the direction remains clear.

For a complete comparison, use a Full Outer merge and expand the right-side key and amount. Coalesce the two key columns for display while retaining separate presence indicators. Match keys as the same data type on both sides.

Microsoft describes these merge choices and matching-column requirements in its Power Query merge overview. The exact UI is an application-review step; the expected classifications here come from the explicit fixture.

Separate absence from an unknown amount

An existing record with a blank amount is different from a missing record. Determine presence using the business key, then validate the amount. Do not use an amount lookup returning zero as the sole test of whether the record exists.

For this fixture, all present amounts are known. Expected signed A-minus-B differences are K1=0, K2=−50, K3=+300 and K4=−250. Treating an absent side as zero is useful for this arithmetic column, but the one-sided status must remain visible.

The differences sum to zero while three keys need explanation. Report exception count and absolute differences as well as the signed net difference so offsetting problems do not disappear.

Document timing differences and resolutions

If one list is an earlier snapshot, a missing key may represent expected timing rather than a defect. Record source extraction times and the agreed comparison window. Reconcile again after the expected arrival period before classifying a delayed record as permanently missing.

Maintain a resolution table with key, issue type, evidence, owner and status. Do not type corrected amounts into the reconciliation output solely to make it balance. Corrections should be traceable to the authoritative source or an approved adjustment.

Test the comparison before using it

Duplicate K2 in B and verify that the key-quality check fails. Add a K5 record with a blank amount and confirm it is classified as present-but-unknown, not missing or zero. Reverse table order and confirm that the unmatched-direction labels reverse appropriately.

Exercise: add currency to both tables and make one K2 amount belong to another currency. Explain why key equality alone no longer establishes amount comparability and how the reconciliation contract should change.

NeuraPath's Data Analytics with Generative AI course connects spreadsheet and SQL reconciliation methods. A complete deliverable includes both unmatched populations and a traceable explanation of every material difference.

Continue learning

This article is part of the Excel and spreadsheet quality sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Validate spreadsheet inputs with business rules.
• Continue with Excel percentage points versus percentage change.