Back to cookbook
Data Analysis

AI Prompt to Find and Remove Duplicate Records in a Dataset

3 views Updated
Share

This AI prompt helps you find and remove duplicate records in a dataset, including near-duplicates that differ only in casing, spacing, abbreviations, or formatting. It's built for analysts, operations staff, and anyone cleaning customer lists, transaction logs, or survey exports in ChatGPT, Claude, or Gemini before the data goes into a report or a database.

Most spreadsheet dedupe tools only catch exact matches on a single column, which misses the messier real-world cases: "Jon Smith" vs "Jonathan Smith," a phone number with and without dashes, or the same order entered twice with a typo in the email field. This prompt asks the model to reason about which fields should match exactly, which should be compared loosely, and how confident it is in each proposed match, so you get a reviewable list instead of a silent delete.

For very large files that exceed what you can paste into a single prompt, pulling only the relevant rows first matters more than the matching logic itself — in that case, running the file through a tool like Context Extractor first can cut the dataset down to the columns and rows actually worth comparing.

Prompt template

Role: You are a data quality analyst reviewing a dataset for duplicate records.

Context:

  • Dataset: [PASTE DATASET OR DESCRIBE FILE AND COLUMNS]
  • Matching key(s): [FIELDS THAT SHOULD BE TREATED AS THE PRIMARY MATCH, e.g. EMAIL AND LAST NAME]
  • Secondary fields to consider for near-matches: [E.G. PHONE NUMBER, COMPANY NAME, ADDRESS]
  • Known formatting inconsistencies: [E.G. SOME ENTRIES HAVE EXTRA SPACES, MIXED CASE, ABBREVIATIONS]

Task:

  1. Identify rows that are exact duplicates on the matching key(s).
  2. Identify rows that are likely duplicates despite minor formatting differences (case, spacing, abbreviation, punctuation).
  3. For each duplicate group, recommend which row to keep based on: [RULE, e.g. MOST RECENT DATE, MOST COMPLETE RECORD].

Constraints:

  • Do not delete or merge any rows yourself — only recommend.
  • Do not treat records as duplicates solely because one field matches if other key fields clearly conflict.
  • Flag anything uncertain as "possible duplicate" rather than guessing.

Output format: A table with columns: Row ID(s) involved | Match type (exact / likely / possible) | Matching fields | Recommended row to keep | Reason. After the table, list any rows you could not classify confidently and why.

Example input

Role: You are a data quality analyst reviewing a dataset for duplicate records.

Context:

  • Dataset: 12 rows of customer signups exported from two marketing tools, columns are Row ID, Full Name, Email, Phone, Signup Date
  • Matching key(s): Email
  • Secondary fields to consider for near-matches: Full Name, Phone
  • Known formatting inconsistencies: some emails have trailing spaces, some phone numbers include dashes and others don't

Row 1: Jon Smith, [email protected], 555-201-4432, 2026-01-03 Row 2: Jonathan Smith, [email protected] , 5552014432, 2026-01-09 Row 3: Maria Lopez, [email protected], 555-887-1120, 2026-01-04 Row 4: M. Lopez, [email protected], 5558871120, 2026-01-15 Row 5: David Chen, [email protected], 555-440-9981, 2026-01-05

Task: same as template above, keep the most recent signup date, output as a table.

Example output

| Row ID(s) | Match type | Matching fields | Recommended row to keep | Reason | |---|---|---|---|---| | 1, 2 | Exact | Email (after trimming whitespace) | Row 2 | Same email once trailing space removed; Row 2 has the more recent signup date (2026-01-09) and a fuller name | | 3, 4 | Likely | Email exact; name abbreviated (Maria Lopez vs M. Lopez); phone matches after removing dashes | Row 4 | Email and phone match exactly once formatting is normalized; Row 4 is more recent | | 5 | No match found | — | Row 5 | Unique email and phone, no other row shares these values |

Rows I could not classify confidently: none in this sample — all five rows had enough matching or distinguishing fields to reach exact or likely confidence. If a sixth row had only a matching last name with a different email and phone, I would flag it as "possible duplicate" rather than merging it automatically.

When to use it

  • You're merging two customer or contact lists exported from different systems
  • A spreadsheet has repeated rows from double form submissions or a failed import retry
  • You need to flag likely duplicates for human review rather than auto-deleting anything
  • You're preparing data for a CRM, mailing list, or database load where duplicates would cause errors

Best practices

  • Tell the model exactly which fields count as the matching key (e.g. email + last name) instead of letting it guess
  • Ask for a confidence label (exact, likely, possible) on each match instead of a flat duplicate/not-duplicate call
  • Request the row numbers or IDs being compared, not just the cleaned output, so you can audit the decision
  • Run smaller batches when the dataset is large — long lists increase the chance the model skips rows silently

Common mistakes

  • Asking the model to just "remove duplicates" without defining which columns must match
  • Letting the model delete rows outright instead of returning a review list you approve first
  • Ignoring formatting differences (extra spaces, inconsistent capitalization, date formats) that hide true duplicates
  • Pasting a dataset too large for the context window and getting incomplete or truncated results

FAQs

How do I prompt ChatGPT to find duplicate rows in a spreadsheet?

Give it the exact column(s) that should count as a match, describe any known formatting issues like extra spaces or inconsistent casing, and ask it to return a table of matches with a confidence level and a recommended row to keep, rather than deleting anything automatically.

Can AI catch near-duplicates that aren't an exact text match?

Yes, if you tell it which secondary fields to check and what kind of variation to expect, such as abbreviated names, missing punctuation, or inconsistent phone number formatting. Without that guidance, most models default to exact-match comparison only.

Why shouldn't I let the model auto-delete duplicate rows?

Because duplicate detection on messy real-world data is probabilistic, not certain. A model can misjudge two similar-looking but genuinely different customers as duplicates, so a reviewable list with reasons is safer than an irreversible delete.

What's the best way to handle a dataset too large to paste into one prompt?

Split it into smaller batches by row range, or extract only the columns relevant to matching before sending it to the model, since very long inputs increase the chance that rows get skipped or summarized instead of individually compared.

Which Cuelara tool can help me prepare a large dataset before running this prompt?

Context Extractor — pulls only the relevant rows and columns out of a large CSV or spreadsheet first, so the deduplication prompt works on a manageable, focused slice instead of truncating. Pair it with Intelligence Score to check whether your matching instructions are specific enough before you run them on the full dataset.

Found this prompt useful? Share it.

Share

More in Data Analysis

Data Analysis

AI Prompt to Clean and Standardize a Messy Spreadsheet Dataset

This is a data cleaning prompt for turning a messy spreadsheet or CSV export into a consistent, analysis ready dataset — built for analysts,…

ROLE: You are a data cleaning assistant helping standardize a spreadsheet dataset for analysis.

CONTEXT: Below is a sample of the dataset. Columns are: [LIST OF COLUMN NAMES]

[PASTE SAMPLE DATA HERE, INCLUDING KNOWN PROBLEM ROWS]

TASK:
1. Review each column and identify formatting inconsistencies (e.g. date formats, capitalization, whitespace, abbreviations, units)
2. Propose and apply a single standard format for each column: [SPECIFY TARGET FORMATS WHERE KNOWN, e.g. dates as YYYY-MM-DD]
3. Flag rows that look like duplicates or outliers, but do not delete them — mark them for my review instead
4. Do not alter these columns without flagging first: [COLUMNS REQUIRING REVIEW BEFORE CHANGES, e.g. customer name, ID numbers]

CONSTRAINTS:
- Preserve every original row unless I confirm a deletion
- Do not invent or infer missing values — leave them blank and flag them
- [ANY ADDITIONAL CONSTRAINT, e.g. keep a specific column's original casing]

OUTPUT FORMAT:
1. The cleaned dataset as a table
2. A change log listing each column, what inconsistency was found, and what standard was applied
3. A separate list of flagged rows (possible duplicates/outliers) with a one-line reason for each flag
Data Analysis

JSON Data Extraction Pipeline

This prompt converts messy, unstructured text (emails, articles, transcripts) into a predictable JSON array that your code can parse without…

Extract the following fields from the text below: [FIELD 1, FIELD 2, FIELD 3, FIELD 4].

Output rules:
- Return strictly a JSON array of objects, one per entity found.
- Use exactly these keys: [key_1, key_2, key_3, key_4].
- If a value is missing or unclear, use null. Never guess.
- Do not wrap the output in markdown code fences.
- Do not add any explanation before or after the JSON.

Text:
"""
[PASTE TEXT]
"""