Finding Duplicates and Gaps in an Excel Sheet With SQL

Two kinds of defects hide in a sheet that looks fine: rows that appear twice and rows that should exist and do not. An invoice list with a duplicated number means a total is overstated. A list with a missing number may mean a lost record or a voided document, and an auditor will ask which. Eyeballing a few thousand rows does not find them. SQL does, in a few lines each. This post shows how to find duplicates, gaps in numeric sequences and gaps in dates, and how to treat what you find. It is a spoke of doing proper analysis on Excel data with SQL.

The sample

A sheet Invoices read as a table:

Read the sheet into DuckDB or SQLite as described in querying an Excel file with SQL in DuckDB, with invoice_no as an integer.

Duplicates

Group by the key and keep the groups with more than one row:

SELECT invoice_no, COUNT(*) AS copies
FROM invoices
GROUP BY invoice_no
HAVING COUNT(*) > 1;

invoice_no | copies
-----------+-------
1002       | 2

To see the rows themselves, count over a window and filter:

SELECT *
FROM (
    SELECT i.*, COUNT(*) OVER (PARTITION BY invoice_no) AS copies,
           ROW_NUMBER() OVER (PARTITION BY invoice_no ORDER BY invoice_date) AS rn
    FROM invoices i
) t
WHERE copies > 1;

rn numbers the copies; deleting rn > 1 keeps one. Decide whether the copies are identical (a double load) or differ (a conflict that needs a person): compare all columns with SELECT DISTINCT * and see whether the group still has more than one row. A duplicate key with different amounts is not a duplicate; it is a data conflict. See finding duplicate rows in an Excel sheet with code for the removal choices and removing duplicates in Excel versus SELECT DISTINCT for the comparison with Excel's button.

The impact is concrete. Here the duplicate overstates revenue by 250. Quantify it: SELECT SUM(amount) - (SELECT SUM(amount) FROM (SELECT DISTINCT * FROM invoices)).

Gaps in a sequence: LAG

An invoice sequence should run 1001, 1002, 1003 and so on. A gap is a value more than one above its predecessor. Compare each distinct value with the previous one:

WITH s AS (
    SELECT DISTINCT invoice_no FROM invoices
),
d AS (
    SELECT invoice_no,
           LAG(invoice_no) OVER (ORDER BY invoice_no) AS prev
    FROM s
)
SELECT prev + 1 AS gap_start, invoice_no - 1 AS gap_end,
       invoice_no - prev - 1 AS missing
FROM d
WHERE invoice_no - prev > 1;

gap_start | gap_end | missing
----------+---------+--------
1003      | 1003    | 1
1006      | 1007    | 2

The DISTINCT removes the duplicate 1002, which would otherwise make the previous value equal to the current one and hide the logic. The output lists ranges, not every missing number, which keeps a gap of five hundred readable. Three numbers are missing in all: 1003, 1006 and 1007.

Gaps and islands

The classic technique groups consecutive values into runs ("islands") so the spaces between them are the gaps. Subtract a row number from the value; consecutive values give a constant:

WITH s AS (SELECT DISTINCT invoice_no FROM invoices),
numbered AS (
    SELECT invoice_no,
           invoice_no - ROW_NUMBER() OVER (ORDER BY invoice_no) AS grp
    FROM s
)
SELECT MIN(invoice_no) AS island_start, MAX(invoice_no) AS island_end, COUNT(*) AS size
FROM numbered
GROUP BY grp
ORDER BY island_start;

island_start | island_end | size
-------------+------------+-----
1001         | 1002       | 2
1004         | 1005       | 2
1008         | 1008       | 1

For 1001 and 1002, invoice_no - row_number is 1000 and 1000. For 1004 and 1005 (row numbers 3 and 4) it is 1001 and 1001. For 1008 (row 5) it is 1003. Each constant defines one island. The gaps are the spaces between island end and the next island start. The technique works for any series of integers, and it is the basis for finding runs of consecutive dates, sessions and uptime periods. Convert dates to day numbers (CAST(date AS INTEGER) in some databases, or date - DATE '2000-01-01') to use it.

Missing dates

For daily data, generate every date and left join the data to see which days have no row:

WITH cal AS (
    SELECT unnest(generate_series(DATE '2025-03-01', DATE '2025-03-08', INTERVAL 1 DAY))::date AS d
)
SELECT cal.d
FROM cal
LEFT JOIN (SELECT DISTINCT invoice_date FROM invoices) i ON i.invoice_date = cal.d
WHERE i.invoice_date IS NULL;

d
----------
2025-03-03
2025-03-06
2025-03-07

The calendar is the list of what should be there; the left join with a null filter shows what is not. Set the start and end from the data (MIN and MAX) rather than typing them. In PostgreSQL write generate_series(DATE '2025-03-01', DATE '2025-03-08', INTERVAL '1 day') without unnest. The same pattern finds missing months, missing hours and missing combinations: join a cross product of all regions and all months to the data and look for nulls, which finds the region that reported nothing in March.

Decide what a gap means

A gap is information, and the interpretation is up to the business.

• A void or canceled document. Numbers are consumed and the record removed or marked. Check for a status column or a separate void list; some jurisdictions require voids to be kept.

• A lost record. The row was deleted or never loaded. This needs a fix and often a conversation with the system owner.

• A sequence that is not continuous by design. Several sources issue numbers from interleaved ranges, or the system skips numbers on rollbacks (database sequences do this routinely). A gap in a PostgreSQL serial column is normal after a failed insert.

• No activity. Missing dates in a sales log may be a Sunday, a holiday or a day the shop was closed.

• A late load. The most recent days are missing because the extract ran early.

Annotate rather than delete. A table of gaps with a reason column (void, lost, closed, unknown) is a deliverable. Then the same query next month can exclude gaps already explained.

Duplicates and gaps across more than one column

Keys are sometimes composite. Check uniqueness on the pair, such as (store, day), and gaps within each group: partition the window.

SELECT store, day,
       day - LAG(day) OVER (PARTITION BY store ORDER BY day) AS days_since_previous
FROM daily_sales
QUALIFY days_since_previous > 1;

QUALIFY is DuckDB's filter on window results. In PostgreSQL use a subquery. This lists, per store, every place where the series jumps. Missing days for one store and not another often point to a feed that failed.

Check your checks

Test the queries on data with known defects before trusting them on real data. The six-row sample above has one duplicate and three missing numbers, and the queries must report exactly those. Add the edge cases: gaps at the start (the sequence should begin at 1001?), a gap at the end (the last invoice might be missing, which no gap query sees without an expected range), and nulls in the key. State the expected range as a parameter, so the end of the sequence is checked too.

From the sheet to a check that runs

If the sheet arrives regularly, keep these queries as a validation step: run them on each load and fail or warn when they return rows. A count of duplicates greater than zero stops the load. A gap list is attached to the report. This turns a one-time investigation into a control (validating row counts after an Excel import is the same idea for counts).

When a gap is not a problem

The sequence is not meant to be continuous. UUIDs, hash ids and database sequences with rollbacks have no meaningful gaps.

Absent means zero. In event data, a missing day may mean no events. Fill with zero for charts, do not report an error.

The data is a sample. A sampled extract has missing keys by design.

Duplicates are legitimate. Repeated product codes across order lines are the structure, not an error.

See DuckDB's page on window functions and PostgreSQL's generate_series. To convert a small result to JSON, use the CSV to JSON converter.