EARTHLOOMGuides   Apps

How to remove duplicate rows in Google Sheets and keep the best

Imports, form submissions, and CRM exports often leave you with hundreds of duplicate rows. The built-in Remove Duplicates keeps only the first occurrence, discarding richer data from later rows. A rule-based approach lets you preserve the most complete record and even combine fields before deleting.

Why this is harder than it looks

Sheets' native Data > Data cleanup > Remove duplicates deletes rows based on position, not quality. You lose phone numbers, emails, or notes that exist only in later duplicates. There is no preview, no merge option, and no undo beyond Ctrl+Z - which vanishes once you close the file. Large datasets make manual review impossible.

Step by step

  1. Define what makes a row a duplicate. Decide which columns form the unique key - typically email, customer ID, or a composite like first+last+company. Insert a helper column with =ARRAYFORMULA(A2:A&"|"&B2:B&"|"&C2:C) to create a single match key per row. This key drives every subsequent step and makes the logic visible.
  2. Score each row for completeness. Add a score column: =COUNTA(D2:Z2) counts non-empty cells across your data columns. Higher score means more fields filled. For weighted scoring (e.g., email matters more than fax), use =IF(D2<>"",10,0)+IF(E2<>"",5,0)+... so the "best" row ranks highest for each key.
  3. Flag the keeper row for each key. In a new column, use =RANK(score, FILTER(scoreRange, keyRange=thisKey), 0)=1 to mark TRUE only for the highest-scoring row per key. If scores tie, add a secondary sort (e.g., most recent date) with a tie-breaker formula. Filter on TRUE to see your keepers before any deletion.
  4. Merge unique values from duplicates into the keeper. For each column you want to merge, use =TEXTJOIN(", ", TRUE, UNIQUE(FILTER(colRange, keyRange=keeperKey))) placed in the keeper row. This collects every distinct non-empty value from all duplicates - emails, phones, tags - into one cell. Do this on a copy sheet so the original stays intact.
  5. Build an undo sheet before deleting. Duplicate the entire working sheet (right-click tab > Duplicate) and name it "Undo_YYYYMMDD_HHMM". On the main sheet, filter to show only rows marked FALSE (the duplicates), select them, right-click > Delete rows. The undo sheet preserves every original row in its original order for recovery.
  6. Validate the result with a quick audit. Run =COUNTUNIQUE(keyRange) on the cleaned sheet and compare to the original unique key count - they should match. Spot-check 10 random keys: open the undo sheet, filter to that key, and confirm the merged keeper contains every distinct value. Fix any missed merges manually.

Common mistakes

Where KeepRight Dedupe fits in

KeepRight Dedupe scans a Google Sheet, groups rows by your chosen key columns, and keeps the best row using rules like most-filled, newest, or oldest. It can merge unique values from duplicates into the kept row, writes a preview sheet for review, and creates an undo sheet so you can restore any deletion.

KeepRight Dedupe support and FAQ

FAQ

Can I keep the most recent row instead of the most complete?

Yes. Add a date column to your data, then sort the sheet by the key column and date descending before scoring. The first row for each key will be the newest. You can also write a custom rank formula that prioritizes date over completeness.

What if two duplicate rows have different values in the same column?

Use TEXTJOIN with UNIQUE on that column for the keeper row, as described in step 4. This combines both values (e.g., "555-1234, 555-5678") so nothing is lost. For single-value columns like status, decide a priority rule (e.g., "Active" beats "Inactive") and apply it with IF/CASE logic.

How do I handle 50,000 rows without the sheet slowing down?

Move the scoring and ranking logic to a separate helper sheet that references the data range, or use Apps Script to process in batches. Array formulas like ARRAYFORMULA and FILTER scale better than row-by-row formulas. Delete the helper columns after you have the keeper flags.