Duplicate contacts sneaking into your CRM import? 5 Google Sheets checks to catch them first

HubSpot dedupes on email, Salesforce on whatever matching rule you set, and Pipedrive barely at all. None of them catch "Jon Smith, [e-mail]" and "Jonathan Smith, [e-mail] " as the same person. Every dupe that slips through means split activity history, double sequences, and reps stepping on each other. These five Google Sheets checks find duplicates before you upload. Assume email is in column C, first name in A, last name in B, company in D, and phone in E.

1. Build a clean email key

=LOWER(TRIM(C2))

Put this in a helper column (say F). Case and stray spaces are the most common reason two identical emails look different to a sheet but identical to your CRM's dedupe, or the other way round.

2. Flag exact email duplicates

=IF(F2="","NO EMAIL",IF(COUNTIF(F$2:F,F2)>1,"DUPE","OK"))

Filter for DUPE and keep the row with the most filled-in fields. =COUNTA(A2:E2) in another column makes picking the winner fast.

3. Catch Gmail dot and plus variants

[e-mail] and [e-mail] are the same inbox. In column G:

=IF(REGEXMATCH(F2,"@(gmail|googlemail)\.com$"),SUBSTITUTE(REGEXEXTRACT(F2,"^[^@+]*"),".","")&"@gmail.com",REGEXREPLACE(F2,"\+[^@]*",""))

Gmail addresses lose dots and plus tags; everything else just loses the plus tag. Re-run check 2 against column G.

4. Match people without an email on name + company

=LOWER(TRIM(A2))&"|"&LOWER(TRIM(B2))&"|"&LOWER(REGEXREPLACE(TRIM(D2),"(?i)[,.]?\s*(inc|llc|ltd|limited|corp|co|gmbh)\.?$",""))

Then =COUNTIF(H$2:H,H2)>1 flags rows where "Acme Inc." and "acme" are the same company. Review these by hand; two real people can share a name.

5. Match on phone digits as a tiebreaker

=RIGHT(REGEXREPLACE(E2,"\D",""),10)

Last 10 digits strips country codes and formatting, so "[phone]" and "555.010.2233" line up. Flag with COUNTIF the same way.

Run all five on a copy, merge or delete the flagged rows, then import only the cleaned sheet. A quick final count, =COUNTIF(I2:I,"DUPE") on your final flag column, should read 0.

---

Rather hand off the cleanup? Send up to 200 rows to the $25 Quick Clean and get back a cleaned CSV (same-email duplicates merged, possible duplicates flagged) plus a change log within 24 hours. No CRM login needed: email us your CSV (reply to your receipt or send it to [e-mail]). Bigger list? Intro $99 covers up to 500 rows, and Growth ($299, up to 2,000) and Bulk ($499, up to 5,000) are on pureservices.store. Want to check the file first? The free CSV preflight checker runs in your browser.