How to find duplicate records after a Salesforce data migration (before users do)

AI-Assisted · Richard C, AIL Studio

The load finished, the record counts are close enough, and sign-off is tomorrow. Then a sales rep finds "Northwind Traders" twice, with Contacts split between the two. Here's how to find those duplicates first.

1. Why matching counts can hide duplicates

A count check only tells you the totals line up. If one record failed and another was loaded twice, the count still matches. Duplicates usually come from:

• a load that was re-run after a partial failure,
• an insert used where an upsert on an External ID was needed,
• keys that differ only by case, spaces or leading zeros (ACC-00412, acc-412, ACC-412), so a Data Loader or API upsert treated them as different records (Salesforce matches External IDs case-sensitively unless the field is set as Unique and case-insensitive).

Salesforce duplicate and matching rules help, but they're tuned for name, email and address matches on new records, and they don't look at your legacy key at all unless you build a rule for it.

If your keys are clean and you only need exact matches, my earlier post covers that: How to find missing and duplicate records after a Salesforce migration (match on an external key). This post is for the messier case, where the same record hides behind a slightly different key.

2. Normalise the key

Export the Accounts from Salesforce with Id, the legacy key field, Name, CreatedDate and a count of Contacts (a report with a Contacts summary works). Put the key in column B and add a normalised key in F2:

=UPPER(LEFT(TRIM(B2),FIND("-",TRIM(B2))))&VALUE(MID(TRIM(B2),FIND("-",TRIM(B2))+1,20))

This trims spaces, makes the key upper case and drops leading zeros from the number after the dash, so acc-412, ACC-00412 and ACC-412 all become ACC-412. It assumes keys shaped like PREFIX-number; adjust it to your format. Add a normalised name in G2 with =UPPER(TRIM(C2)).

Example data: ACC-00412 and acc-412 both normalise to ACC-412.

3. Group and count by key

In H2, count each normalised key, and in I2 count the raw key exactly:

=COUNTIF($F$2:$F$13,F2)
=SUMPRODUCT(--EXACT($B$2:$B$13,B2))

Change 13 to your last row. A key count above 1 is a duplicate. If the exact count is also above 1, the raw key repeats as-is (usually a re-run). If only the normalised count is above 1, it's a hidden duplicate that an exact check would miss.

In the org itself, the same check is one SOQL query (swap in your field's API name):

SELECT Legacy_Key__c, COUNT(Id) FROM Account
GROUP BY Legacy_Key__c HAVING COUNT(Id) > 1

In most English-locale orgs, SOQL's text comparison here is case-insensitive, so it catches ACC-412 vs acc-412 but not leading zeros or trailing spaces. Use the spreadsheet for those.

4. Separate true duplicates from legitimate repeats

Not every repeat is a duplicate. In J2:

=IF(H2=1,"Unique",IF(COUNTIFS($F$2:$F$13,F2,$G$2:$G$13,G2)<H2,"Review: key collision",IF(I2>1,"Exact duplicate","Hidden duplicate")))

Example data: two hidden duplicates, one exact duplicate, one key collision, and two "Granite Works" rows that are unique.

• Exact / Hidden duplicate: same normalised key, same name. Safe to merge.
• Review: key collision: same normalised key, different names ("Delta Freight" vs "Delta Freight Ltd"). It might be one company, or two source records whose keys collided after normalising. Check the source before touching it.
• Same name, different keys ("Granite Works" twice): these stay Unique. Two branches can share a name. Don't merge on name alone.

5. Choose the survivor and repoint children first

Deleting a duplicate Account deletes or orphans what hangs under it, so pick the survivor and move the children before deleting anything. In K2, keep the record with the most Contacts:

=IF(OR(J2="Unique",LEFT(J2,6)="Review"),"",IF(E2=MAXIFS($E$2:$E$13,$F$2:$F$13,F2),"Keep (survivor)","Repoint "&E2&", then delete"))

If two records tie, pick the older CreatedDate by hand. Then repoint Contacts, Opportunities and Cases from each loser to its survivor (Data Loader update on AccountId, or Salesforce's merge for Accounts, which moves related records for you). Only then delete. Fix the key on the survivor so the next upsert finds it.

Example data: a one-screen summary for the sign-off call.

6. Pre-go-live checklist

• Run the key count on every object you loaded, parents first.
• Normalise keys the same way in the source and in Salesforce.
• Review every key collision against the source; don't merge them automatically.
• Repoint child records before deleting a duplicate.
• Mark the External ID field Unique so the next load can't insert the same key twice.
• Re-run the count after the cleanup and keep the summary with your sign-off.

Formulas were checked in LibreOffice Calc. The same functions exist in Excel and Google Sheets, but I haven't tested them there, so check them once in your own copy.

For the exact-key side of this check, RecordBridge is a browser tool that compares before-and-after Account, Contact and Opportunity exports and reports missing and extra records, blank or duplicate keys, and wrong-parent links. It matches keys exactly, so normalise them first: RecordBridge.