Remove Duplicates in Google Sheets Without Losing Distinct Records

Define the columns that make a duplicate

The sample has five data rows. E01, Ada, Seoul appears twice, so one copy can be removed under an exact full-row rule. E01, Ada, Busan differs in city and stays in the result. It may represent another valid record or a source conflict; the duplicate-removal step does not decide which.

Write the rule in plain language before selecting columns. For example: remove rows only when member_id, name, and city are the same. If you instead need one current address per member, you also need evidence such as a verified update timestamp or a trusted source priority.

Preview the result with UNIQUE

Import or place the sample in A1:C6, with the header in row 1. Keep E2:G6 empty and enter =UNIQUE(A2:C6) in E2. Copy the three headers to E1:G1. This keeps the original table available while you inspect the four-row result.

UNIQUE returns rows in their first-appearance order. Its normal behavior retains one representative of each distinct row. The optional exactly_once argument means something different: TRUE removes every member of a repeated group. That is useful for finding unrepeated rows, but it is not the usual request to keep one copy.

Keep one copy of each distinct row:
=UNIQUE(A2:C6)
Expected data rows: 4

Keep only rows that never repeat:
=UNIQUE(A2:C6,FALSE,TRUE)
Expected data rows: 3

Remove duplicates from a reviewed copy

When a fixed cleaned table is the desired output, duplicate the source sheet or copy its values to a working tab. Select the exact table range including the header and every associated column. Avoid selecting only the ID cells, which makes the intended record boundary unclear.

Open Data > Data cleanup > Remove duplicates. Mark that the data has a header row and select member_id, name, and city for this example. Run the operation, then verify that five data rows became four and that the Busan entry for E01 remains.

1. Create a working copy and record the original five-row count.
2. Select A1:C6, then open Remove duplicates.
3. Enable the header setting and compare all three columns.
4. Verify the remaining records against the expected output.
5. Keep a note of the compared columns with the cleaned file.

Check the resulting records, not just the count

A result with four rows could still be wrong if the selected rule removed a distinct record and retained an unwanted row. Compare the record values as well as the total. In this example, both E01 cities must remain after full-row deduplication.

Google documents that the Remove duplicates command can regard matching values as duplicates despite differences in letter case, formatting, or formulas. Do not use the command as a case-sensitive identity validator. If case matters to your identifier specification, use a separate exact-comparison review and demonstrate the intended behavior on a small copy before modifying the full dataset.

Clean carefully before making the result permanent

Trailing spaces and other hidden text can make apparently identical entries differ. If normalization is appropriate, create reviewed helper values using a consistent whitespace rule, then deduplicate on those values. Check whether normalization merges keys that were previously distinct; the cleaner-looking result is not automatically the correct business result.

Keep formula output separate from a final snapshot. If you need a stable handoff, copy the reviewed UNIQUE result and paste values into a new output area. Do not assume future rows will be included in a bounded A2:C6 formula; update the range deliberately. The Column Harbor checker does not execute Sheets cleanup commands or decide duplicate identities.

Related help: Check nonbreaking spaces before deciding rows are duplicates