Reconcile two lists

What reconciling two lists means

Reconciling answers three questions about two lists of identifiers:

• In A, not in B: invoices with no payment, customers missing from the CRM, codes you stock that the supplier dropped.
• In B, not in A: payments with no invoice, new sign-ups, new codes in the catalogue.
• In both: the matched items, and how many times each appears on each side (a duplicate payment shows up as a count of 2).

Unlike a full comparison, a reconciliation looks at one column on each side and ignores the rest. The two columns don’t need the same name, and the lists don’t need to be the same length or in the same order.

In Excel: COUNTIF or MATCH

Put list A in column A of one sheet and list B in column A of a sheet called B. Next to the first value in list A enter:

=IF(COUNTIF(B!A:A, A2)=0, "not in B", "in both")

and fill it down. Do the same on sheet B pointing back at list A to find what is only in B. Filter each helper column to get the lists. Things to watch:

• COUNTIF ignores case and treats * and ? in your values as wildcards, so a code like AB*1 can match more than you meant.
• MATCH with exact matching (=ISNA(MATCH(A2, B!A:A, 0))) still treats * and ? in text as wildcards, and it treats the number 1001 and the text "1001" as different values, which is common when one list came from a CSV. XMATCH (Microsoft 365 and Excel 2021) matches exactly without wildcards by default.
• Trailing spaces make values that look identical fail to match. Wrap both sides in TRIM, or clean the data first.

In Excel with dynamic arrays: FILTER

In versions of Excel with dynamic arrays (FILTER is in Microsoft 365 and Excel 2021 and later), one formula spills the whole list:

=FILTER(A2:A500, COUNTIF(D2:D400, A2:A500)=0, "none")

gives the values in A2:A500 that are not in D2:D400. Swap the ranges for the other direction, and use COUNTIF(...)>0 for the values in both. The same caveats about case, wildcards and spaces apply.

In RowDiff: one step, three lists

1. Drop the file with list A on A and the file with list B on B (or use one workbook with two sheets).
2. Click Reconcile two lists and choose the column on each side.
3. Tick Ignore case and Ignore extra spaces if ACME Ltd and acme ltd should count as the same.

You get the three lists side by side with counts. Numbers and number-like text match each other (1001 equals "1001"), wildcards mean nothing, blanks are skipped and counted separately, and values with leading zeros stay as they are. Download the result as an .xlsx with one sheet per list, or as a single CSV with a Where column.

Reconcile your lists above

When to compare rows instead

If the question is not only “is it there?” but also “did its details change?” (the amount on a matched invoice, a customer’s address), use Compare rows with the identifier as the key column. Matched rows are then compared field by field, and you get the in-A-only and in-B-only rows as removed and added rows in the same view.

Related: Compare two Excel sheets · Compare two CSV files · Compare two Excel files for differences