DEV Community

Sales List Rescue
Sales List Rescue

Posted on

Dedupe a messy lead list in Google Sheets in 10 minutes (no add-ons, 5 formulas)

#googlesheets #crm #productivity #dataquality

You bought a list, merged three trade-show exports, or inherited a "master_leads_FINAL_v3.csv". Before it goes anywhere near HubSpot, Salesforce, or Pipedrive, run this. No add-ons, no scripts, just five formulas.

1. Normalize the email into a helper column

In a new column next to your emails (say email is in column C):

=LOWER(TRIM(CLEAN(C2)))
Enter fullscreen mode Exit fullscreen mode

CLEAN drops invisible characters, TRIM drops stray spaces, and LOWER makes Jane@Acme.com and jane@acme.com the same key.

2. Flag duplicates with a running count

Next column:

=COUNTIF($D$2:D2, D2)
Enter fullscreen mode Exit fullscreen mode

The first time an email appears you get 1; every repeat gets 2, 3, and so on. Filter for anything greater than 1 to see exactly what you're about to remove.

3. Catch blank and broken emails 

=IF(D2="","BLANK",IF(REGEXMATCH(D2,"^[^@\s]+@[^@\s]+\.[a-z]{2,}$"),"OK","BAD"))
Enter fullscreen mode Exit fullscreen mode

Rows marked BAD are usually typos like gmail,com or a phone number pasted in the wrong column. Fix or drop them now; a CRM import will reject them or, worse, accept them.

4. Split full names the CRM can map

If you only have "Full Name" in column B:

=SPLIT(TRIM(B2)," ",TRUE,TRUE)
Enter fullscreen mode Exit fullscreen mode

Then keep the first piece as First Name and join the rest as Last Name. Salesforce Leads require Last Name, so don't skip this.

5. Standardize company names for account matching 

=PROPER(TRIM(REGEXREPLACE(E2,"(?i),?\s*(inc|llc|ltd|corp)\.?$","")))
Enter fullscreen mode Exit fullscreen mode

Acme, Inc., ACME INC, and acme collapse to Acme, so contacts land on one account instead of three.

Then filter and export

Filter to running count = 1 and status = OK, copy to a new tab, and download as CSV (Sheets exports UTF-8 by default). That's your import file.

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 slrescue@mail.grokbot.com). 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.

Top comments (0)