---
title: "Remove duplicate rows across two files | Oplema"
description: "Learn to find bookings that appear in two files, remove them with Unique in Strata on the right key and check every removed row in the Duplicate output."
url: "https://oplema.com/learn/many-sources/remove-rows-in-two-files"
updated: "2026-11-04"
---

Lesson 2 of 9 · Lab

# Remove rows that appear in two files

Unique keeps the first row for each key and sends later matches to its Duplicate output. The result depends on what you call a match: too many key columns and nothing is a duplicate.

By default Unique compares every column. Rows that are the same booking but differ in one column, such as `Source`, aren't duplicates by that rule.

Old Town's till at [fictional Kramerica Bikes](https://oplema.com/learn/export-to-report/first-data-flow) failed from September 14 to 20, so Leila, who manages Old Town, had that week's bookings keyed at Station Road. When the till came back, her staff keyed them again at Old Town. Both files hold the same bookings, with the same `booking_id`. The stack from the last lesson is here, with a **Unique** beside it that compares every column.

Unique starts by comparing every column. How many duplicates will it find?

None. The repeated rows match in every column but `Source`, which says which file each copy came from.

## Find the week that's there twice

1. **Unique** is selected and red in this live [Strata](https://oplema.com/strata) flow, Oplema's browser app for data prep, because it has no input yet. Drag from **Union** to **Unique**: **OUTPUT UNIQUE** holds all 1,298 rows, and **OUTPUT DUPLICATE** is empty.
2. Under **Match On**, keep only what identifies a booking, so the repeated rows go to **OUTPUT DUPLICATE**. Check: **OUTPUT DUPLICATE** holds 80 rows. If you're stuck
   Clear the top box, so no column is selected, then select `booking_id` alone. With `Source` selected as well, nothing is a duplicate, because each copy has a different `Source`.
3. Read **OUTPUT DUPLICATE**: all 80 are from Old Town, dated September 15 to 20, as Old Town is closed on Mondays. Scroll right: every one says `input_3`.
4. Open **OUTPUT UNIQUE**: 1,218 rows.

What each output holds

Unique holds 1,218 bookings and Duplicate 80, worth €2,054.50 with VAT. 1,218 + 80 = 1,298. Of the 80, 73 are finished hires with 82 bikes between them.

## Name the rows you removed

Unique keeps the first copy of each booking. Union passes rows on in wire order, so Station Road's copies, `input_2`, come first and stay. Old Town's own copies, `input_3`, are the ones removed. Both copies say `Old Town` in `shop`, so no figure moves, but Leila can see exactly which of her rows went.

Left in, the week counts 82 of Old Town's bikes twice. Its September bikes out would read 429 instead of 347, and the smallest shop would look short of bikes for the wrong reason.

Your line for Marit: "1,218 bookings. 80 rows were Old Town's bad week, counted twice."

See the [Unique reference](https://oplema.com/docs/strata/prep/unique). For later: [Finding and removing duplicate rows](https://oplema.com/learn/data-preparation/unique-and-duplicates) explains how to decide which copy to keep.

## Common duplicate mistakes

- **Keying on every column.** A copy that differs in one column, such as a file name or an export date, survives. Key on what identifies the thing.
- **Keying on too little.** Two different bookings can share a date and an amount. Use the ID if there is one.
- **Throwing the duplicates away unread.** Keep the Duplicate output where you can count it, so a removal can be explained.

## Rows in two files: SQL and Excel

```
SELECT  *  FROM  stacked
QUALIFY  row_number ()  OVER  ( PARTITION  BY  booking_id  ORDER BY
  CASE  source  WHEN  'harbour'  THEN  1  WHEN  'station'  THEN  2  ELSE  3  END )  =  1 ;
```

The `ORDER BY` decides which copy is first, as wire order does here. Ordering by the source's name would keep Old Town's copies instead, because `old_town` sorts before `station`. `SELECT DISTINCT` is the every-column rule and would remove nothing. In Excel, **Remove Duplicates** asks which columns to compare; select only `booking_id`, and copy the sheet first, because the removed rows are gone.
