How to audit duplicates: Google Sheets & Salesforce guide

## Top ways to build a Salesforce duplicate reportBelow are three levels of sophistication: manual, no‑code automation, and AI‑agent driven. You can start with the basics and layer automation as your volume grows.---### 1. Manual and traditional methods (3–7 options)#### 1.1 Use Salesforce Duplicate Record Set reports1. In Salesforce Setup, open `App Launcher` and search for `Duplicate Rules` and `Matching Rules`.2. Ensure matching rules (for Leads, Contacts, Accounts) are active and tuned (e.g., email exact match, fuzzy name match).3. When duplicates are detected, Salesforce creates `Duplicate Record Sets`.4. Go to the Reports tab.5. Click `New Report` and search for the type `Duplicate Record Sets`.6. Add filters like `Record Type = Lead` and date filters.7. Add columns: Duplicate Rule, Record Count, Created Date, Owner.8. Run the report and export to CSV if you want to review in Google Sheets.Official docs: https://help.salesforce.com/s/articleView?id=sf.managing_duplicates_overview.htmPros: Native, secure, respects existing rules. Cons: Static, still requires a lot of manual reviewing and merging.#### 1.2 Classic lead or contact reports with grouped fields1. Go to `Reports` → `New Report`.2. Choose `Leads` (or `Contacts`, `Accounts`).3. In the outline, group rows by fields likely to indicate duplicates, like `Email`, `Phone`, or `Account Name`.4. Add a row‑level summary count of records per group.5. Filter where `RowCount > 1` to surface groups with duplicates (or use a bucket field or custom filter logic to isolate them).6. Save the report as `Potential Lead Duplicates`.7. Export to CSV for deeper analysis in Google Sheets.Docs (Reports): https://help.salesforce.com/s/articleView?id=sf.reports_builder_edit.htmPros: Flexible, no admin access needed. Cons: Easy to miss fuzzy duplicates and cross‑object duplicates.#### 1.3 Excel/Sheets style manual dedupe1. Export any of the above reports to CSV.2. Open in Google Sheets via `File → Import` or upload to Drive.3. In Sheets, use `Data → Remove duplicates` to quickly remove exact matches on Email or other fields. Docs: https://support.google.com/docs/answer/139717094. Use `=COUNTIF(A:A, A2)` to flag values that appear more than once.5. Add filters and conditional formatting (e.g., duplicates highlighted in red).6. Manually decide which record is the “winner” (most complete, most recent activity).7. Update Salesforce manually by opening each record and merging or editing.Pros: Transparent and very flexible for one‑off audits. Cons: Extremely time‑consuming at scale; error‑prone when re‑keying changes.#### 1.4 Use Salesforce built‑in merge tools1. From a Lead, Contact, or Account record in Salesforce, click `View Duplicates` (if enabled by your admin).2. Salesforce shows potential duplicates based on active matching rules.3. Select records to merge.4. For each field, choose the surviving value.5. Confirm merge.Docs: https://help.salesforce.com/s/articleView?id=sf.duplicate_rules_merging.htmPros: Safe, governed merge process. Cons: Record‑by‑record; doesn’t give overview of the problem.---### 2. No‑code automation with Google Sheets and workflow tools#### 2.1 Live sync Salesforce to Google SheetsYou can avoid constant CSV exports by using a live connector.Options include:- Salesforce Data Connector for Google Sheets (from Salesforce Labs)- Third‑party tools like Zapier, Make, or Coupler.ioExample with Salesforce Connector for Sheets:1. In Google Sheets, go to `Extensions → Add-ons → Get add-ons` and search for `Salesforce`.2. Install the official Salesforce Connector.3. Open it via `Extensions → Salesforce Connector`.4. Authenticate with your Salesforce org.5. Build a query selecting Leads/Contacts, including fields like Id, Email, Phone, Company, OwnerId.6. Schedule automatic refresh (e.g., hourly, daily).Reference: https://support.google.com/docs/answer/9071123 (using connectors and add-ons)Now your duplicate‑checking sheet updates automatically without manual exports.#### 2.2 No‑code dedupe workflows in SheetsOnce the data flows into Sheets, you can create a reusable “dedupe workbook”:1. Use a dedicated tab `Raw_Salesforce` for imported data.2. Create a `Dedupe_View` tab that references raw data with formulas such as: - `=UNIQUE(Raw_Salesforce!B:B)` to get unique emails. - `=COUNTIF(Raw_Salesforce!B:B, B2)` to count how many times each email appears.3. Add a helper column `Is_Duplicate` with a formula like `=IF(COUNTIF(Raw_Salesforce!B:B, B2)>1, "Yes", "No")`.4. Filter `Is_Duplicate = Yes` to get a working list.5. Add a `Keep_Record_Id` column where your team chooses the winning Salesforce Id.Pros: Reusable, teams can collaborate in real time. Cons: Still needs humans to push changes back into Salesforce.#### 2.3 Push clean data back to Salesforce with no‑code toolsTo avoid manual updates:1. Use Zapier, Make, or another integration platform.2. Trigger on new or updated rows in a specific Sheets tab (e.g., `Approved_Merges`).3. For each row, call Salesforce’s `Update Record` or `Merge` actions, using the `Keep_Record_Id` and `Duplicate_Record_Id` fields.4. Log results back into Sheets (status column: Success / Error).Docs (Salesforce + automation platforms) are typically found under each tool’s help center, e.g., https://help.salesforce.com and https://support.google.com/docs for Sheets.Pros: Removes repetitive updates, ideal for RevOps and agencies running periodic cleanups. Cons: Logic is brittle; if your rules change, you must rebuild workflows.---### 3. Scaled, AI‑agent methods with SimularNow imagine your “dedupe analyst” is an AI computer agent that can:- Log into Salesforce and Google Sheets like a human.- Run reports, export data, and refresh connected Sheets.- Apply nuanced business rules (e.g., keep record with open Opportunities, preserve custom fields).- Document every action for audit.Simular Pro is designed to behave like a power user across your desktop, browser, and cloud apps.#### 3.1 Agents that run the full duplicate workflowA Simular AI agent can:1. Open Salesforce, navigate to Reports, and run your `Duplicate Record Sets` and `Potential Lead Duplicates` reports.2. Export them, open Google Sheets, and paste or import data into the correct tabs.3. Apply dedupe formulas, filters, and sort logic you’ve standardized.4. Flag edge cases (e.g., conflicting owners, different account hierarchies) into a `Human_Review` tab.5. For all clear‑cut duplicates, open the relevant Salesforce merge pages and complete merges according to rules you’ve described.Pros: End‑to‑end automation, uses your existing UI and tools, no API development. Cons: Requires initial design of the workflow and clear written rules.#### 3.2 Always‑on duplicate monitoringInstead of a quarterly cleanup, you can:1. Schedule Simular Pro agents to run nightly.2. They open Salesforce, apply filters like `CreatedDate = YESTERDAY`, run duplicate checks for new records only.3. Update a Google Sheet dashboard summarizing new duplicates found, merged, and pending review.4. Send summaries via email or Slack.Because Simular agents are production‑grade and every action is logged, RevOps and agencies can treat them like reliable junior analysts—only faster and tireless.#### 3.3 Transparent, tweakable logicSimular emphasizes transparent execution: you see every step the agent takes. When business logic changes (e.g., SDR territories, priority accounts), you update the workflow description once and the agent’s behavior updates without rebuilding brittle no‑code flows.This is how you go from “cleaning duplicates when it hurts” to a continuous, agent‑driven hygiene system that quietly protects your Salesforce and keeps every Google Sheets dashboard honest.