How to find missing and duplicate records after a Salesforce migration (match on an external key)

AI-Assisted

By Richard C, AIL Studio

The migration finished and the record counts look about right: 9 Accounts in the old system, 8 in Salesforce. Close enough? Not quite. "About right" can hide a record that never arrived, one that arrived twice, and one that showed up from nowhere, all cancelling each other out. This post shows how to find each of those with two CSV exports and a spreadsheet.

1. Why you can't match on record IDs

Salesforce gives every record a new 15/18-character Id when it's inserted. The old system's IDs don't survive the move, so you can't line the two exports up by Id. You need a key that exists on both sides.

2. Pick a stable external key

Most migrations carry the old system's ID into a custom field, often marked as an External ID (for example Legacy_ID__c). That's the key. Export it on both sides:

• Source: the old system's export, with its ID column.
• Target: a Salesforce report or Data Loader export with Legacy_ID__c, Name and Id.

If there's no shared key, stop here. Matching on name or email is a different (and much fuzzier) job, and this method won't do it reliably.

Example data: the Source sheet with the D4 status formula showing in the formula bar. Fake Accounts; across both sheets there are six problems. Calc's formula bar shows $Target.A$2; in Excel and Google Sheets the same reference is written Target!A$2.

Put the source on a sheet called Source and the target on a sheet called Target, each with the key in column A.

3. Check for blank and duplicate keys first

A blank or repeated key makes the missing/extra check lie, so count each key on its own sheet first. On Source, in C2:

=SUMPRODUCT(--EXACT(A$2:A$10,A2)) 

Change 10 to your last row. Why not COUNTIF? COUNTIF ignores case, so ACC-1007 and acc-1007 would count as the same key. EXACT is case-sensitive, which is what an External ID comparison needs. It also doesn't trim spaces, which is deliberate: a key with a stray space really is a different key, and you want to see it, not have it silently fixed.

Anything above 1 is a duplicate key. An empty cell in column A is a blank key.

4. Match both directions

Now one status formula per row. On Source, D2:

=IF(LEN(A2)=0,"Blank key",IF(C2>1,"Duplicate key",IF(SUMPRODUCT(--EXACT(Target!A$2:A$9,A2))=0,"Missing in target","OK"))) 

Example data: one record never arrived (ACC-1003), one looks missing because of case (ACC-1007), a duplicate key and a blank key.

On Target, put the same count in D2 (=SUMPRODUCT(--EXACT(A$2:A$9,A2))) and the mirror-image status in E2:

=IF(LEN(A2)=0,"Blank key",IF(D2>1,"Duplicate key",IF(SUMPRODUCT(--EXACT(Source!A$2:A$10,A2))=0,"Extra in target","OK"))) 

Example data: ACC-1002 was loaded twice, acc-1007 doesn't match its source key, and ACC-1009 has no source record.

Reading the results:

• Missing in target (ACC-1003): the record never loaded. Check the load's error file.
• Extra in target (ACC-1009): a record with no source. Maybe it was created by hand after the load, or came from another file.
• Duplicate key in target (ACC-1002): the load ran twice, or an insert was used where an upsert should have been. Note the source row says "OK", so you only catch it by checking both sides.
• Case mismatch (ACC-1007 vs acc-1007): the same record flagged on both sides. Fix the key, don't loosen the match.
• Blank and duplicate source keys: fix these in the source before trusting anything else.

5. Repeat per object, parents first

Do Accounts first, then Contacts, then Opportunities. A missing Account usually explains missing Contacts and Opportunities underneath it, so fixing parents first saves you from chasing the same gap three times.

What this doesn't check

This only answers "did every record arrive exactly once?". It doesn't compare field values, and it doesn't check that a Contact sits under the right Account. For that, see How to catch wrong-parent Contacts after a Salesforce migration and the SOQL version, How to audit Contact–Account parents after a Salesforce migration with SOQL.

If you'd rather load before-and-after CSVs in a desktop browser and get one findings report for missing records, changed fields and wrong parent Accounts, I made RecordBridge (US$49, one-time).