---
title: "How to find and remove duplicate rows | Oplema"
description: "Learn to find duplicates with COUNTIF or GROUP BY HAVING, remove them with ROW_NUMBER or Remove Duplicates and choose which copy of a record to keep."
url: "https://oplema.com/learn/data-preparation/unique-and-duplicates"
updated: "2026-11-04"
---

Lesson 8 of 15 · Lab

# Finding and removing duplicate rows

To find duplicate rows, count how often each key appears, and anything above one is a duplicate. To remove them, keep one row per key. The harder part is choosing which copy is true, because copies often disagree.

Exact duplicates match in every column, while key duplicates share only an identifier. Rows 101, pending and 101, booked are key duplicates, and they have to be resolved by a rule.

Here are [fictional Vandelay Coffee Supply](https://oplema.com/learn/data-preparation/what-is-data-prep)'s 18 clean invoice rows, sorted newest export first.

The two invoices that arrive twice
| invoice\_id | exported\_on | status | amount\_eur |
| --- | --- | --- | --- |
| INV-1008 | 2026-09-01 | pending | 540 |
| INV-1008 | 2026-10-01 | booked | 540 |
| INV-1010 | 2026-09-01 | booked | 900 |
| INV-1010 | 2026-10-01 | booked | 900 |

Unique keeps the first row per `invoice_id`. If you reverse the sort, does the total change?

No, and that's the trap. Both copies of each invoice carry the same amount, so the total can't see which one you kept.

## Remove duplicates, then reverse the sort

1. Here, in a live flow in [Strata](https://oplema.com/strata), Oplema's browser app for data prep, **Unique** is selected. **Output Unique** shows one row per invoice, with INV-1008 `booked` at the top.
2. Open **Output Duplicate**. These two rows are what you removed. Keeping them visible means you can show your work.
3. Select **Summarize**, the last block. Note `total` and `rows`.
4. Select **Sort**. In the first key, change **Descending** to **Ascending**.
5. Select **Summarize**: same numbers. Now select **Unique**, open **Output Unique** and scroll to INV-1008. Its status has flipped.
6. Put the sort back to **Descending** and check that INV-1008 reads `booked`.

What each output holds

Unique keeps 16 rows totaling €14,410, and Duplicate holds 2 rows totaling €1,440, in either direction. Reversed, INV-1008 reads `pending`.

## First means first in the order you gave it

The position of a row in a file proves nothing. Here you have a real reason for the order, since a later export knows more. The status matters because [filtering rows](https://oplema.com/learn/data-preparation/filter-rows) keeps only booked invoices, so the wrong copy would quietly cost €540.

If copies disagree on something you can't rank, sorting won't settle it. [Unique](https://oplema.com/docs/strata/prep/unique)'s **Split** mode sends every copy to Duplicate so you can ask the source's owner.

The total falls from €15,850 to €14,410 here. That €1,440 is the overlap between the two exports, and Vandelay's sales report counted it twice. The finance report's €10,168 is still unexplained.

## Common duplicate mistakes

- **Choosing too small a key.** In a table of invoice lines, `invoice_id` alone deletes real sales, so you'd want `invoice_id` plus `line_id`. Ask what one row should represent.
- **Removing without looking.** Keep the removed rows where you can read them.
- **Leaving the survivor to chance.** Without a stated sort, the copy you keep depends on the file.

## Duplicates in SQL and Excel

```
SELECT  invoice_id,  COUNT ( * )  FROM  invoices
GROUP BY  invoice_id  HAVING  COUNT ( * )  >  1 ;

SELECT  *  FROM  (
  SELECT  * ,  ROW_NUMBER ()  OVER  (
    PARTITION  BY  invoice_id  ORDER BY  exported_on  DESC )  AS  rn
  FROM  invoices
) t  WHERE  rn  =  1 ;
```

The first query finds duplicate keys, and the second keeps the newest copy of each. `SELECT DISTINCT` removes only exact duplicates. In Excel, `=COUNTIF(A:A, A2)>1` flags repeats. **Remove Duplicates** keeps the first occurrence, so sort first, and it reports only how many rows it removed.
