Deduplication - Clean & Remove Duplicate Data Records

Duplicate records are more than a cosmetic problem. They inflate counts, split customer history, waste outreach, distort reporting, and can cause the same person or organization to be treated as several different records. The difficult part is not finding rows that look alike. It is deciding what “duplicate” means, which record should survive, and how to preserve useful information from every version.

This guide uses a safety-first workflow for MySQL, Microsoft Excel, Power Query, and Microsoft Access. The specific buttons and queries differ, but the reliable process is the same: define the match rule, preview the affected records, choose a survivor rule, preserve a recoverable copy, remove or merge only the reviewed duplicates, and validate the result.

Start with a duplicate policy - not a delete button

Before changing data, write down the fields that define identity. An email address may be enough for a newsletter list, but it is rarely enough for a complete customer record. Business data may require a combination such as normalized company name, street address, city, state, and postal code. Product data might use a manufacturer number plus a vendor.

• Normalize comparison values. Decide whether case, spaces, punctuation, abbreviations, and empty values should be treated as equivalent.
• Choose the survivor. Keep the newest verified record, the most complete record, the record tied to transactions, or another explicit winner.
• Choose merge rules. A duplicate may contain a newer phone number, a missing company name, or consent evidence that must not be discarded.
• Protect relationships. Orders, notes, subscriptions, downloads, and audit records may point to a record you plan to remove.
• Preserve evidence. Export the source, record the duplicate count, and keep a list of the affected IDs before making a destructive change.

Profile the problem first

A useful duplicate report groups normalized values and shows only groups with more than one member. For example, this MySQL query finds repeated non-empty email addresses without deleting anything:

SELECT LOWER(TRIM(email)) AS normalized_email,
       COUNT(*) AS record_count
FROM customers
WHERE email IS NOT NULL
  AND TRIM(email) <> ''
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1
ORDER BY record_count DESC;

Run similar reports for each candidate key. Review false positives and decide whether blank values belong in the comparison. Names alone are especially risky: two people can share a name, and one person can use several valid names or addresses.

MySQL: rank, preview, then remove

MySQL 8 window functions make the survivor rule visible. The following example keeps the most recently updated record in each normalized email group, using the highest ID as a final tie-breaker:

WITH ranked AS (
  SELECT id,
         email,
         updated_at,
         ROW_NUMBER() OVER (
           PARTITION BY LOWER(TRIM(email))
           ORDER BY updated_at DESC, id DESC
         ) AS duplicate_rank
  FROM customers
  WHERE email IS NOT NULL
    AND TRIM(email) <> ''
)
SELECT *
FROM ranked
WHERE duplicate_rank > 1
ORDER BY email, duplicate_rank;

That query is a preview. Inspect it before deleting anything. When the result is correct, materialize the reviewed IDs into a temporary table so the deletion target cannot change between inspection and execution:

CREATE TEMPORARY TABLE duplicate_ids AS
SELECT id
FROM (
  SELECT id,
         ROW_NUMBER() OVER (
           PARTITION BY LOWER(TRIM(email))
           ORDER BY updated_at DESC, id DESC
         ) AS duplicate_rank
  FROM customers
  WHERE email IS NOT NULL
    AND TRIM(email) <> ''
) ranked
WHERE duplicate_rank > 1;

SELECT c.*
FROM customers c
JOIN duplicate_ids d ON d.id = c.id;

START TRANSACTION;
DELETE c
FROM customers c
JOIN duplicate_ids d ON d.id = c.id;

-- Validate counts and relationships here.
-- Use ROLLBACK if anything is wrong; COMMIT only after review.

Do not copy this query blindly into production. Replace the table, fields, and survivor rule with the ones established for your data. If related tables reference the duplicate IDs, migrate those relationships to the survivor inside the same controlled transaction before deletion. For syntax and window-function behavior, consult the MySQL 8 reference.

Excel: make a copy and choose the comparison columns

For a one-time cleanup in current desktop versions of Excel, first copy the source worksheet or save a separate working file. Select the data, choose Data > Remove Duplicates, and select only the columns that define a duplicate. Excel keeps the first occurrence it encounters, so sort deliberately before removal if a particular row should survive.

For review before deletion, use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values, or use an Advanced Filter to copy unique records to another location. Microsoft warns that Remove Duplicates permanently deletes the duplicate values, which is why the working copy matters. See Microsoft’s current Excel duplicate-removal instructions.

Power Query: make the cleanup repeatable

Power Query is a better fit when the same type of file arrives repeatedly. Import the data, normalize the fields used for matching, select the duplicate-key columns, and choose Home > Remove Rows > Remove Duplicates. The steps remain in the query, so the process can be refreshed against later files.

If the survivor matters, do not depend on an incidental row order. Add an explicit priority field, group records by the duplicate key, and verify the selected record or merged values. Microsoft’s current Power Query guidance covers keeping and removing duplicate rows.

Access: find duplicates before rebuilding the unique set

In Access, use the Find Duplicates Query Wizard to identify candidate groups. Include the primary key and enough descriptive fields to distinguish false matches. Create a backup copy of the database before using an update, delete, or make-table query.

For a controlled cleanup, create a query that identifies the chosen survivor for each group, update dependent records to reference that survivor, and only then remove the reviewed duplicate rows. A unique index can prevent the same problem from returning, but add it only after normalizing the values and resolving legitimate exceptions.

Validate the cleaned data

1. Run the original duplicate report again and compare the remaining groups with the expected exceptions.
2. Reconcile source and destination row counts: original rows minus removed rows should equal the final total.
3. Confirm orders, subscriptions, downloads, notes, and other relationships still point to valid records.
4. Spot-check the winner in several duplicate groups and confirm useful values were merged where required.
5. Record who performed the cleanup, when it occurred, the rule used, the reviewed ID list, and where the backup is stored.

Prevent duplicates at intake

Cleanup is expensive; prevention is cheaper. Normalize important values as data enters the system, validate obvious formatting problems, use appropriate unique constraints, and design imports as idempotent upserts rather than unconditional inserts. Keep marketing consent, transactional history, and identity evidence separate enough that merging a contact never erases why or when a permission was granted.

The best deduplication job is not the one with the shortest deletion query. It is the one that leaves a trustworthy dataset, a clear survivor for every group, intact relationships, and enough evidence to explain exactly what changed.