Skip to content
trending

AI Tools

Remove duplicates in Google Sheets without deleting valid orders

Choose the right duplicate key in Google Sheets, compare Remove duplicates with UNIQUE, and verify the result using a worked order-table example.

By 7 min read
Remove duplicates in Google Sheets without deleting valid orders

👁 113 reads

To remove duplicate rows in Google Sheets, select the complete table and use Data > Data cleanup > Remove duplicates. Before confirming, decide which columns identify the same record: matching email addresses do not make two different orders duplicates.

Decide what one row represents

I begin a cleanup by writing one sentence: “Each row represents an order,” or “Each row represents a customer.” This is the decision that determines which records should survive. The spreadsheet cannot infer your business meaning from the fact that two cells look alike.

Consider this illustrative order table. The amounts are sample values for explaining the calculation, not figures from a real business.

Order ID Customer Amount
A101 alex@example.test 40
A101 alex@example.test 40
A102 alex@example.test 25
A103 sam@example.test 60
A104 sam@example.test 15

The raw table has 5 rows and totals 180. If the second A101 row is a repeated import, the intended result has 4 orders totaling 140. But if you deduplicate on the customer column alone, you collapse Alex’s two orders and Sam’s two orders into just two customer records. That is a different dataset.

The distinction matters outside sales. In an attendance sheet, the same person can appear at several sessions. In an invoice export, the same invoice can legitimately have several line items. In a mailing list, repeated addresses may be the very thing you want to remove. The correct key depends on the row’s purpose.

For an order-line export, the key might be the combination of order ID and line ID. For a customer directory, it might be an assigned customer identifier. I avoid treating a person’s displayed name as a unique identifier when the source system offers something more precise.

Use the cleanup command on a copy

Make a working copy of the table before applying an operation that removes rows. Give the copy a name that explains the rule, such as orders-dedup-by-order-id. Keep the imported table unchanged until the result is accepted.

Select the table, open Data > Data cleanup > Remove duplicates, indicate whether the selection has headers, and choose the comparison columns. Google notes that identical values can count as duplicates despite differences in letter case, formatting or formulas. Selecting a single cell can cause the operation to cover the surrounding range, so explicitly selecting your table is easier to inspect. Google’s cleanup instructions.

I would write down the intended comparison before clicking the final button:

  • One row per order: compare the order identifier, after checking what that identifier means in the source export.
  • One row per order line: compare the order identifier together with the line identifier.
  • One copy of each complete record: compare every relevant data column.
  • One row per customer: use the customer identifier, then decide separately how to reconcile different contact details.

The last case needs more than deletion. Two records for the same customer might contain different phone numbers or a newer delivery address. Choosing which values to preserve is a reconciliation task. A duplicate-removal command does not decide which version is authoritative.

Use UNIQUE when you want a separate result

The UNIQUE function creates a distinct result from a source range. Google documents that it returns unique rows in their first-appearance order. Its optional exactly_once argument has a different purpose: returning only entries that do not repeat. UNIQUE documentation.

For the sample data in A2:C6, a separate output area can use:

text
=UNIQUE(A2:C6)

This compares complete rows. It is appropriate for the sample because both A101 rows are identical across all three columns. It would not remove a second A101 record whose amount differed. That disagreement deserves investigation rather than silent erasure.

Compare it with:

text
=UNIQUE(B2:B6)

That formula produces a list of distinct customers. It does not produce a cleaned order table. The output is useful for counting or reviewing customers, but attaching the original amounts beside it would not reliably associate each customer with the correct orders.

The exactly_once option is also easy to misread. In the sample, asking for complete rows that occur exactly once excludes the repeated A101 record entirely. It does not keep a representative A101 order. Use that option only when your question is “Which records never repeat?”

Goal Suitable approach Check afterwards
Remove repeated imported rows Cleanup on a copied table Remaining rows and total
Keep the source and show distinct rows UNIQUE on the full data range Output width and contents
Build a list of customers UNIQUE on the customer column No order amounts attached by position
Find entries that occur only once UNIQUE with exactly_once Repeated entries are absent

Flag repeated order IDs before removing anything

For the sample table, select A2:C6, open Format > Conditional formatting, and choose Custom formula is. Use this rule:

text
=COUNTIF($A$2:$A$6,$A2)>1

The fixed range counts occurrences in the order-ID column. The row reference changes for each row being formatted, while $A keeps the comparison on the ID. In this particular example, both A101 rows should be highlighted; A102, A103 and A104 should not. Choose a visible fill and finish the rule. Google documents custom formatting rules.

Highlighting identifies a repeated key, not a permission to delete. Change the second A101 amount to a different value in a disposable copy: it should still be highlighted, but now you have conflicting records to reconcile. If your export uses blank IDs, wildcards or identifiers where letter case matters, do not apply this sample rule unchanged. COUNTIF’s matching rules matter to the result.

This gives me a review step before destructive cleanup. I can compare the full highlighted records, decide which import is authoritative, and preserve the other orders placed by the same customer.

Verify the result before using it elsewhere

I check the sample table against three independent expectations: the surviving order identifiers, the number of rows, and the sum of amounts. A row count alone cannot show that you removed the right row.

For a larger file, choose a few records to inspect deliberately. Include a genuine duplicate, a customer with several valid orders, and a record whose values differ between imports. This creates a meaningful review set instead of checking only the first screen of the spreadsheet.

Whitespace deserves a separate pass. Google’s trimming tool removes ordinary extra spaces but does not remove non-breaking spaces. The UNIQUE documentation also warns that hidden text can make apparently identical rows differ. If two entries survive unexpectedly, compare the actual cell contents before repeatedly running cleanup. Google’s cleanup instructions.

Do not normalize every column indiscriminately. A product code, customer identifier and free-text comment have different meanings. First preserve the source, then make the intended normalization visible in a working column or copied table.

Once the checks agree, record the rule used and the date of the cleanup. That note lets another person reproduce the decision when next month’s export arrives. The useful deliverable is a table whose remaining rows have a clear meaning.

FAQ

Should I select only the column containing duplicates?

Select the complete table when removing rows, then choose the comparison columns in the dialog. This keeps the record structure explicit while you decide the matching rule.

Can two rows have the same email and still be valid?

Yes. In the example, one customer placed different orders. An email-only rule would collapse valid transactions.

Does UNIQUE change the imported source table?

It creates a formula result from the selected range. Keep the output in a separate area with room for its rows and columns.

Why do visually identical values survive?

Hidden characters or different underlying contents can matter. Inspect the cells and your comparison columns before removing more data.

Is a smaller row count proof that cleanup worked?

No. Compare identifiers, valid repeat activity and relevant totals against the original. The sample result should contain four orders totaling 140.

Google · Your sourcesQuantslant higher in your Google - one clickAdd to preferred sources →

Sources

Kinetyra

// read next

More on this from QUANTSLANT

Piotr Olszewski

Editorial team

Independent AI publication. Models and tools are tested on real tasks, prices are checked against official rate cards, and articles are updated when things change.

read nextExport ChatGPT conversations and check the archive before movingai toolsExport ChatGPT conversations and check the archive before movingExport your ChatGPT conversations, open the current archive viewer, and check the files before deleting chats or moving to another workspace.

Most read

  1. 1Best AI Video Generator Apps for Your Phone (2026)
  2. 2Claude Opus 5.5 Motion Graphics: 60s Showreel from One Prompt
  3. 3Wan 2.2 on RTX 4090: timings, VRAM and four test clips
// site map
Quantslant in numbers 1,543posts in archive16news from the last 7 days18video models in the observatory
🎬 AI Video Benchmarks: Higgsfield DoP vs OpenAI Sora • Higgsfield vs Runway Gen-3 • AI Video Credit Calculator
× ‹ enlarge ›