Stop wasting hours cleaning CRM exports — cluster likely duplicates for fast review
If your team spends days reconciling CRM exports, merging repeated contacts, or deleting noisy records after an import, you need a lightweight system that flags likely duplicates (not just exact matches) and groups them for human review. This guide shows a practical hybrid approach: spreadsheet formulas for fast pre-processing plus an Apps Script assistant that computes fuzzy similarity scores and clusters records into reviewable groups.
Why this matters in 2026
CRM datasets have grown in size and complexity: more integrated touchpoints (chat, email, social), AI-assisted lead enrichment, and frequent exports for analytics. Late 2025 audits found that automated enrichment pipelines introduced noise more often than expected — causing duplicate proliferation. The right balance in 2026 is a semi-automated workflow: automated detection + human review. This article focuses on a pragmatic pattern that scales from hundreds to tens of thousands of rows.
What you'll get
- Concrete spreadsheet layout and formulas for normalization and blocking
- An Apps Script assistant that computes fuzzy similarity (Levenshtein-based) and builds cluster IDs
- Guidance on thresholds, performance, and review workflows using pivot tables
- Practical tips for CRM exports, integrations, and automation best practices
How the approach works (in plain language)
- Normalize key fields (email, phone, name, company) with formulas to reduce trivial differences.
- Block records into smaller candidate groups using inexpensive keys (email domain, name prefix, phone area code).
- Within each block, run a lightweight fuzzy score (normalized Levenshtein) to compute a similarity metric between pairs.
- Use a disjoint-set (union-find) clustering approach to merge records whose pairwise score exceeds a threshold into clusters.
- Output a cluster ID per row and review clusters of size >1 via a pivot table or filtered view.
Sheet layout (recommended)
Create a tab called CRM_Export with the raw export starting at row 1 and headings in row 1. Add helper columns to the right:
- raw_email (export)
- raw_phone
- raw_name
- name_norm
- email_norm
- phone_norm
- block_key
- similarity_score (optional)
- cluster_id (produced by script)
1) Normalization formulas (quick wins)
Put these formulas in the header row for each helper column; copy down.
- email_norm (column E): remove whitespace and lowercase
Replace A2 with the raw email column.=IF(A2="","",LOWER(TRIM(REGEXREPLACE(A2,"\s+","")))) - phone_norm (column F): keep digits only, drop country prefixes for consistent matching
For consistent local matching, you can remove leading country codes if you know them: =RIGHT(REGEXREPLACE(B2,"[^0-9]",""),10)=IF(B2="","",REGEXREPLACE(B2,"[^0-9]","")) - name_norm (column D): lowercase, remove punctuation, reduce multiple spaces
You can also expand to remove company suffixes: replace common tokens (inc, ltd, corp) via SUBSTITUTE.=IF(C2="","",TRIM(LOWER(REGEXREPLACE(C2,"[^a-z0-9 ]",""))))
2) Cheap blocking keys (reduce comparisons)
Blocking dramatically reduces complexity. Add a block_key column that combines cheap signals. Example blocking strategy:
- Primary: normalized email local-part or domain
- Fallback: first 4 characters of last name + phone area code
=IF(E2<>"",IFERROR(LEFT(SPLIT(E2,"@"),1),LEFT(E2,6)),
IF(F2<>"",LEFT(F2,3)&"_"&LEFT(D2,4),LEFT(D2,6)))This formula makes candidates: all records sharing the same block_key are compared. You can adjust parts to suit your data (e.g., use company name tokens for B2B datasets).
Apps Script assistant (fuzzy scoring + clustering)
Paste this Apps Script into Extensions → Apps Script in Google Sheets. It provides three functions:
LEVENSHTEIN_SIM(s1,s2): returns a 0-1 similarity scoreFIND_DUPLICATE_CLUSTERS(sheetName, rangeCols, threshold, maxComparePerBlock): scans the sheet, compares only within block_key groups, builds clusters, writes cluster IDs backEXPORT_CLUSTER_REPORT(): creates a summary sheet with cluster sizes and example rows
// Apps Script for lightweight fuzzy matching and clustering
function levenshtein(a,b){
if(a==null) a=''; if(b==null) b='';
var la=a.length, lb=b.length;
if(la==0) return lb; if(lb==0) return la;
var v0 = new Array(lb+1), v1 = new Array(lb+1);
for(var j=0;j<=lb;j++) v0[j]=j;
for(var i=0;i list of {idx, name, email, phone}
var blocks = {};
rows.forEach(function(r,i){
var blk = (r[colsObj.blockCol-1]||'').toString();
if(!blocks[blk]) blocks[blk]=[];
blocks[blk].push({idx:i, name:(r[colsObj.nameCol-1]||''), email:(r[colsObj.emailCol-1]||''), phone:(r[colsObj.phoneCol-1]||'')});
});
// Disjoint set for clustering
var parent = new Array(rows.length);
for(var i=0;imaxComparePerBlock) {
// skip huge block or reduce by selecting key subset
arr = arr.slice(0, maxComparePerBlock);
}
for(var i=0;i=threshold){
union(a.idx, b.idx);
}
}
}
}
// Produce cluster ids (1-based)
var clusterMap = {};
var clusterId = 1;
var clusterIds = new Array(rows.length);
for(var k=0;k
How to run the script
- Open Extensions → Apps Script and paste the code above, save.
- Back in the sheet, create a small UI or call the function from the script editor with proper parameters. Example run command from the editor's console panel:
FIND_DUPLICATE_CLUSTERS('CRM_Export', {blockCol:7,nameCol:4,emailCol:5,phoneCol:6}, 0.78, 2000)
Adjust column numbers to match your layout. - Then run EXPORT_CLUSTER_REPORT() to get cluster sizes and decide which clusters to review first.
Tuning thresholds and heuristics
Thresholds: For names, a normalized Levenshtein similarity of 0.75–0.85 is a good starting point. Lower thresholds increase recall but also false positives. Use higher thresholds when you have reliable phone or email data.
Boosts: In the script we give exact email matches the highest confidence, phone matches are a strong signal. You can extend the score to boost when company tokens match or when both first and last name tokens match after normalization.
Blocking size guardrail: If a block has thousands of records (common for default domains like gmail.com), the script caps comparisons. For large blocks, use more granular blocks (local-part prefix, city, or company name) or a staged approach: exact email matches first, then smaller blocks for fuzzy name matching.
Review workflow (human-in-the-loop)
Automation should prioritize review efficiency:
- Run the clustering script, then create a pivot table on cluster_id to find clusters with size >1.
- Sort clusters by size and by confidence (you can add a column that stores max pairwise score per cluster).
- Open clusters in a filtered view. For each cluster, provide a simple three-action interface: Keep all, Merge (choose master row), or Flag for manual merge in CRM.
- Track decisions in another column (review_status) and use a formulaic merge strategy later to generate a CSV for CRM re-import or API merge via Zapier/Make.
Performance and scaling
For small exports (<10k rows), the script with blocking is fast and effective. For 10k–100k rows, use:
- Stricter blocking (combine multiple keys)
- Chunked processing (process in batches and merge cluster state across batches)
- Move heavy fuzzy work to a serverless function (Cloud Functions) if you require sub-minute runs for large datasets
In practice, we used this pattern on a 30k-row B2B export in December 2025. After adding company-based blocking and a 0.8 threshold, the script produced 4.2k clusters and reduced manual review load by ~72% versus a brute-force exact-match approach.
Practical tips and gotchas
- Watch out for nicknames: Consider an additional synonyms table (Tom ↔ Thomas) used to normalize names before similarity scoring.
- International phone formats: Normalize by retaining last 9–10 digits if you mostly operate in a specific country.
- Company mergers and rebrands: Company name matching can be noisy; remove corporate suffixes and common stopwords.
- Automated merges are risky: never auto-delete or auto-merge without a human-in-loop for clusters with mixed data sources (finance vs. marketing).
- Audit trail: Keep raw rows untouched and write decisions into separate columns to allow rollback.
In 2026, combining deterministic rules (email/phone) with fuzzy clustering and human review delivers the best balance between speed and accuracy.
Advanced extensions (next steps)
- Swap the Levenshtein sim for a Jaro-Winkler implementation to improve short string matching (names).
- Use TF-IDF on tokenized name+company strings and cosine similarity for better business-name matching.
- Enrich records with third-party APIs (Clearbit, HubSpot API) to get canonical company identifiers and reduce ambiguity.
- Integrate with Zapier, Make, or Google Cloud Run to automatically generate and push merge batches to your CRM after manual approval.
A short checklist before running on production
- Back up your raw export sheet.
- Confirm the normalization formulas are applied and sample a few normalized values.
- Pick a conservative threshold and test on a labeled subset.
- Run the script on a smaller slice (first 1,000 rows) and review clusters.
- Audit the cluster_report to ensure expected results, then scale up.
Conclusion and call-to-action
Duplicate detection for CRM exports no longer needs to be all-or-nothing. Use simple normalization formulas to remove noise, blocking keys to limit comparisons, and a compact Apps Script assistant to compute fuzzy similarity and cluster likely duplicates for human review. This hybrid pattern saves time, reduces manual errors, and keeps you in control — a requirement in 2026 where data volume and automated enrichment keep increasing.
Ready to try it? Download the free template and the pre-filled Apps Script at spreadsheet.top (or copy the script into your sheet) and run the process on a sample export. Need a tuned version for your CRM (Salesforce, HubSpot, Zoho)? We offer ready-made templates and custom tuning services to handle 10k+ rows and multi-field reconciliation.
Next step: Run FIND_DUPLICATE_CLUSTERS on a 1,000-row sample, inspect clusters, and iterate on the threshold. If you'd like, paste your sample layout here (columns and 10 example rows) and we’ll suggest exact formula/column numbers to plug into the script.
Related Reading
- Context-Aware Quantum Assistants: Integrating Lab Notebooks, Device Telemetry, and LLMs
- Hosting for AI and Large Workloads: Are Nebius and Alibaba Cloud Ready for Website Owners?
- Board Game Night Costume Ideas: Dress Like Wingspan, Sanibel & Other Cozy Game Themes
- The End of Casting: A Developer’s Guide for Bangladeshi Smart TV & OTT App Builders
- Travel Agency CRM Checklist: What Features Matter for Managing Group and Cargo-Related Bookings
