Find duplicate records in my data, including near-duplicates
Finds exact and fuzzy duplicates, separates them from legitimate repeats, and gives you Excel or SQL steps to resolve them safely.
The prompt
Help me find and resolve duplicate records in my data.
Data: [PASTE SAMPLE ROWS and column names]
What one record should represent: [e.g. "one customer", "one order", "one invoice line"]
Tool: [Excel / Google Sheets / SQL / Python / Power BI]
Known causes: [OPTIONAL - e.g. "imports from two systems", "manual entry", "form submitted twice"]
Steps:
1. Define what makes a record unique here (the key), and flag it if no clean key exists.
2. Find exact duplicates on that key.
3. Find near-duplicates: spelling variants, extra spaces, case differences, swapped first and last names, the same phone, email, or address in different formats, and the same transaction entered twice minutes apart.
4. Separate true duplicates from legitimate repeats (e.g. a customer who really did order twice).
5. Propose a survivorship rule: which record to keep and which fields to merge from the others.
Return:
1. Results from my sample: Group | Records | Why they match | True duplicate or legitimate? | Record to keep.
2. Matching rules ranked from strictest to loosest, with the risk of false matches for each.
3. Step-by-step instructions and formulas or queries for my tool (e.g. COUNTIFS, conditional formatting, Remove Duplicates, GROUP BY with HAVING COUNT(*) > 1).
4. How the duplicates affect my totals and counts.
5. One way to prevent duplicates at the source.
Do not delete anything automatically: give me a review list first, and remind me to keep a copy of the original data before removing rows.
Highlighted [brackets] are blanks to replace with your own details.
Fill in the blanks and use this promptMore Data & Analysis prompts
What to fill in
- PASTE SAMPLE ROWS and column names
- e.g. "one customer", "one order", "one invoice line"
- Excel / Google Sheets / SQL / Python / Power BI
How this prompt is built
This prompt follows the Few-Shot structure (Few-shot prompting). A few-shot prompt includes one or more worked examples of the input and the output you want, so the model can copy the pattern instead of guessing it.
Related prompts
- Explain a complicated Excel formula in plain EnglishBreaks down a long or nested Excel formula step by step, says what it actually returns, and points out hidden risks.
- Build a simple spreadsheet to track ticket sales for an eventStep-by-step setup for a ticket sales tracker with a data entry form, search, totals, and payment status for fundraisers and community events.
- Reconcile two reports that show different numbersPinpoints why two dashboards or reports disagree on the same metric and gives a fix plus a short explanation for stakeholders.
- Measure the impact of a change when an A/B test isn't possibleChooses and plans a quasi-experimental method like difference-in-differences or synthetic control, with assumptions and checks.