Compare Two Spreadsheets to Find Missing Records

To compare two spreadsheets and find missing records, match each row on a stable ID rather than its name. Check both directions, set duplicate IDs aside, and review name differences separately from missing records.

The stable ID is the key to a reliable comparison

A stable ID is the best way to find records in one spreadsheet but not another. Use an order reference, customer reference, employee ID, or another value that is meant to identify the same record in both lists. Names can be edited, abbreviated, or spelled differently without creating a new record.

Before comparing, decide what each list represents. One might be the current register and the other an export from a separate system. Keep an unchanged copy of each. Put the lists on separate tabs in the same workbook, with IDs in column A and names in column B. In the example below, the tabs are called Current and Other. A tab name is just a place to look, not evidence that its contents are correct.

Check that the IDs refer to the same kind of record and use the same format. An ID stored with leading zeros may not match a version that has lost them. Blank IDs cannot be matched reliably. If no stable ID exists, do not treat a name-only comparison as proof that a record is missing. Make a review list instead, using other details such as an address or transaction date to help confirm identity.

A worked example separates missing rows from changed names

Matching the Current and Other tabs by ID shows which records are absent, even when their names differ. Suppose Current contains REF-A with the name “Main Office,” REF-B with “Service Desk” on two rows, and REF-C with “Supply Desk.” Other contains REF-A with “Main Offices,” REF-B with “Service Desk,” and REF-D with “Filing Room.” These are illustrative rows, not a recommended ID format.

REF-C is in Current but not Other, so it belongs on a list of records missing from Other. REF-D is in Other but not Current, so it belongs on a separate list of records missing from Current. REF-A exists in both lists. Its different name needs review, but it is not a missing record. REF-B also exists in both lists, yet its repeated appearance in Current needs a duplicate check before anyone decides which row should remain.

Write down which direction matters for the task. If Other is a list of completed work, “missing from Other” may mean work that needs checking. If Other is merely an older export, the same result could be an expected new record. The comparison finds differences; a person still has to decide what they mean.

A lookup formula finds missing rows in Google Sheets or Excel

A COUNTIF formula is enough to compare two Google Sheets lists for missing rows or reconcile two Excel lists without coding. With IDs in column A on both tabs, put this formula in an empty column beside the first data row on Current: =IF(A2="","Blank ID",IF(COUNTIF(Other!$A:$A,A2)=0,"Missing","Present"))

Copy the formula down alongside the Current records. It checks whether the Current ID appears anywhere in Other. Filter the result column to “Missing” to see candidates for records absent from Other. “Blank ID” keeps an empty cell from being presented as a missing record. The formula works when the lists are tabs in the same Google Sheets file or Excel workbook. Replace Other with the actual tab name if yours differs.

To find records missing from Current, put the corresponding formula beside the rows on Other and change the sheet reference from Other to Current. Comparing in both directions matters: a check from Current alone cannot reveal REF-D from the example.

COUNTIF checks whether an ID appears, not whether its name agrees or its row is unique. Also inspect ID formatting before trusting the results. For example, a reference with a missing leading zero may look like a missing record because the values no longer match.

Duplicate IDs need their own review before reconciliation

A duplicate ID is a separate problem from a missing ID. An existence check can report “Present” even when the same reference appears on several rows, as REF-B does in Current. Treating those rows as separate records could lead to repeated messages, payments, or updates.

Beside each list, use =IF(A2="","",COUNTIF($A:$A,A2)) and copy it down. Filter for counts greater than one. Do the same on the other tab, because duplicates can occur on either side. The formula counts occurrences in its own tab; it does not decide whether the repeated rows are mistakes. Keep the original rows visible while you investigate.

Check whether repeated IDs represent an accidental copy, separate events attached to one account, or an ID that was never intended to be unique. Compare the relevant dates, amounts, and source records before deleting or merging anything. If several legitimate events share an account ID, that account ID is not a sufficient matching key for an event-level comparison. Use a unique event reference if one exists.

Set unresolved duplicates aside when making a final missing-record list. Otherwise, a clean-looking “Present” result can hide a problem that matters more than an absent row.

Names that differ are review items, not automatic matches

A name difference does not make a record missing when its stable ID matches. In the worked example, REF-A appears in both lists, although “Main Office” and “Main Offices” are not identical. Keep that row out of the missing-record report and put it on a separate name-review list.

Review whether the difference is harmless formatting, an outdated name, or a sign that the ID was assigned to the wrong record. Spaces and capital letters are often easy to assess. A substantially different name deserves a check against the source system before either spreadsheet is changed. Do not overwrite a name simply because the other list looks newer.

If there is no dependable ID, software may suggest possible matches from names and other fields. Those suggestions are leads for a person to confirm, not proof of identity. Keep unmatched and uncertain rows separate. AI can help group descriptions that use different wording, as in finding common themes in survey responses with AI, but grouping similar text is not the same task as deciding whether two rows are the same record.

Record the reason for each correction so that the next comparison does not repeat the same argument.

A checked difference list is more useful than an automatic edit

The useful output is a short, reviewable difference list, not an automatic merge of the spreadsheets. Keep separate views for missing from Other, missing from Current, duplicate IDs, blank IDs, and matched IDs with differing names. Include the source tab and the original row details so someone can trace each finding back to its source.

Check a few records in each category against the systems or documents that produced the lists. Confirm that both exports cover the same period and use the same definition of a record. If one file includes archived entries and the other does not, the formulas may be correct while the resulting “missing” list is misleading.

A spreadsheet formula is usually enough for an occasional comparison that a person will review. Consider an automation tool only when the same exports arrive regularly, the ID rules are settled, and someone owns the exceptions. Even then, route ambiguous matches and duplicates to review instead of silently changing records. If the result will drive messages, such as an invoice reminder workflow, approve the reconciled list before any messages are sent.

Save the original files and the reviewed result. On the next run, that record of decisions helps distinguish a new discrepancy from one already investigated.

Sources consulted

• Microsoft Support (support.microsoft.com)
• Google Sheets Help (support.google.com)

Start a free course