How to remove duplicates when spreadsheet rows are not identical

Clues in the source data

• Customer counts exceed known accounts.
• One invoice number appears under formatted vendor names.
• Names match but identifiers conflict.
• Deleting exact duplicate rows barely changes the issue.

Why this gets messy

Duplicate entities often differ in capitalization, spaces, punctuation, phone formatting, address abbreviations, or a newer email. They may also be legitimate people with similar names. Blind deletion can merge separate entities or discard current information.

From the field · Small-business CRM administrator

Merge duplicate CRM contacts without collapsing two different customers

A CRM export contains Riya Shah and R. Shah with the same made consistent phone number, plus two different Alex Martins who share a company. Exact-row removal misses the first pair, while name-only matching would merge the second pair incorrectly.

Columns used in the example

contact_id · full_name · email · phone · company · updated_at · source

Follow it with your own file

1. make consistent email, phone, spacing, and casing into comparison-only fields.
2. Create exact, high-confidence, and review-required match tiers using more than one identifier.
3. Choose a survivor rule for each attribute, such as latest verified phone but earliest acquisition source.
4. Retain a crosswalk from every retired contact_id to the surviving contact_id.

Check: Review all uncertain pairs, confirm that merged groups do not contain conflicting verified identifiers, and reconcile contacts before and after as survivors plus retired IDs. Keep the merge crosswalk for later joins.

Duplicate-detection methods

A method you can follow

1. Declare the entity and grain: State what one row represents and which identifiers are authoritative. Event tables should retain repeated customer IDs; a customer master may not.
2. make consistent comparison fields: Create comparison-only forms of names, emails, phones, and addresses while preserving original display values.
3. Score and review candidates: Match exact IDs first, then combine signals for probable duplicates. Send conflicts and low-confidence pairs to review.
4. Apply survivorship and preserve lineage: Define which record or attribute wins, produce a crosswalk, and validate entity counts and downstream joins.

Exact rows and duplicate entities differ

Spreadsheet tools compare cell values. Businesses care about entities. Two customer rows can represent one person despite a changed email; two identical transaction rows can both be valid purchases. The expected grain decides whether repetition is an error.

Use confidence tiers

An exact trusted ID match can be automated. A name-plus-phone match may be probable. A similar name in the same city may only be possible. Tiers prevent fuzzy convenience from turning uncertainty into irreversible merges.

The short version

• Define whether rows represent people, accounts, invoices, transactions, or events.
• Use stable identifiers before fuzzy names whenever possible.
• Separate exact, probable, and possible matches.
• Keep an audit crosswalk so downstream references are not orphaned.

Doing these steps in Prepyr

Prepyr accepts CSV files only. Export the worksheet or table you need as a UTF-8 CSV first; workbook formulas, formatting, macros, charts, and other sheets will not be included. Describe the result you need: Find and merge duplicate customer records when spreadsheet rows are not identical. Check the columns selected, preview any proposed changes, and compare the output with the source before applying or exporting it.

Prepyr steps used here: Duplicate profiling · Preview before apply · Changed Row diff · Workflow reuse

Final checks before using the result

• what one row represents documented
• Keys identified
• Original values preserved
• Uncertain matches reviewed
• Survivorship approved
• Crosswalk exported

Questions readers usually ask next

Which columns should define duplicates?

Use fields that define the entity or event, such as account ID or invoice number plus supplier—not whichever columns happen to be filled.

Is fuzzy matching safe to automate?

Only for high-confidence rules validated on known examples. Probable and conflicting matches need review.

What is a survivorship rule?

It defines which record and attributes remain, such as newest verified email or source-system priority.