Python Excel AutomationPractical guides for developers
How-to
Advanced Data Transformation And CleaningDeep dive

Find Gaps in a Numbered Sequence with pandas

Check that invoice, cheque or ticket numbers in an Excel export run without gaps: extract the number from IDs such as INV-2026-000123, check each series separately, list missing numbers as compact ranges, catch duplicates and numbers issued out of date order, and write an exceptions report.
Validation & data qualitypandas

Auditors ask for it, tax authorities in many countries require it, and it catches real problems: every number in a sequence of invoices, credit notes, cheques or delivery notes should be accounted for. A gap can be a voided document nobody recorded, an export that missed rows, or an invoice raised outside the system. In Excel people sort the column and scan it by eye, which works until the list has twenty thousand rows and three numbering series. pandas finds every gap, duplicate and out-of-order number in a few lines. This page is part of Validating Excel Data with Python.

Prerequisites

Bash
pip install pandas openpyxl

documents.xlsx is a document register exported from an accounting system: document number, issue date and amount, in no particular order. A second file, void_register.xlsx, lists documents that were voided after their number was issued. The register contains three numbering series: sales invoices for 2026 (INV-2026-000001 onwards), credit notes for 2026 (CN-2026-…), and the end of the 2025 invoice series, whose numbers continue from 3801. Each series must be checked on its own: the credit-note numbers are not gaps in the invoice sequence.

From document IDs to exceptions Document IDs are split into a series prefix and a number. Each series is checked separately for missing numbers, duplicates and numbers whose issue date is earlier than a lower number's. The findings go to an exceptions report. Document IDs INV-2026-000123 Split series + number Per series groupby Checks gaps, dupes, order Exceptions report

Step 1 — Split the series and the number

The number is the digits at the end; the series is everything before them. One regular expression with two named groups does both, and fails loudly on IDs that do not fit the pattern:

Python
import pandas as pd

docs = pd.read_excel("documents.xlsx", parse_dates=["IssueDate"], dtype={"DocumentNo": str})
parts = docs["DocumentNo"].str.extract(r"^(?P<Series>.*?)(?P<Number>\d+)$")
bad = docs[parts["Number"].isna()]
assert bad.empty, f"IDs without a trailing number: {bad['DocumentNo'].tolist()}"
docs = docs.join(parts.assign(Number=parts["Number"].astype(int)))
print(docs.groupby("Series")["Number"].agg(["count", "min", "max"]).to_string())

Reading the ID column as text matters. A column of plain numbers such as 000123 read as integers loses its leading zeros, and a mixed column can come back as floats, where 123.0 breaks the extraction.

Step 2 — Missing numbers per series

For each series, the numbers that should exist are every integer from the lowest to the highest; the missing ones are the difference between that range and the numbers present. Sets make the difference explicit:

Python
def missing_numbers(numbers):
    present = set(numbers)
    return sorted(set(range(min(present), max(present) + 1)) - present)

gaps = {series: missing_numbers(g["Number"]) for series, g in docs.groupby("Series")}
for series, missing in gaps.items():
    print(f"{series:10} missing {len(missing)}: {missing}")

The range starts at the lowest number present, which is right for a series that continues from last year like INV-2025-. For a series that must start at 1, check the start explicitly: a missing first invoice is also a gap.

Some of those gaps are already explained. Numbers voided in the accounting system do not appear in the register export but are recorded in the void register; remove them before anyone investigates:

Python
voids = pd.read_excel("void_register.xlsx", dtype={"DocumentNo": str})
vparts = voids["DocumentNo"].str.extract(r"^(?P<Series>.*?)(?P<Number>\d+)$")
voided = {(s, int(n)) for s, n in vparts.itertuples(index=False)}
unexplained = {s: [n for n in m if (s, n) not in voided] for s, m in gaps.items()}
for series, missing in unexplained.items():
    print(f"{series:10} unexplained {missing}")

That leaves four unexplained numbers in the 2026 invoice series and none in the credit notes. From here on the report works with the unexplained gaps. Keep the void register as the source of truth for voids, rather than a list typed into the script, so the check stays correct as new documents are voided.

Step 3 — Report gaps as ranges

A list of individual numbers gets long when a whole block is missing. Collapse consecutive numbers into ranges, the way an auditor would write them. Consecutive runs share the same value of number − position:

Python
def as_ranges(numbers):
    if not numbers:
        return []
    s = pd.Series(numbers)
    runs = s.groupby(s - pd.RangeIndex(len(s))).agg(["min", "max"])
    return [f"{a}" if a == b else f"{a}–{b}" for a, b in runs.itertuples(index=False)]

for series, missing in unexplained.items():
    print(f"{series:10} {', '.join(as_ranges(missing)) or 'no gaps'}")
Missing numbers collapsed into ranges On the left the sorted numbers present around the first gap, 55, 56, 60, 61. On the right the missing numbers 57, 58 and 59 reported as the single range 57 to 59. Number 55 56 60 61 Missing 57–59 numbers present reported gap as_ranges()

Step 4 — Duplicates

A number used twice is as serious as one missing: it can mean a document was re-issued with the same number, or the export duplicated a row. Separate the two by checking whether the duplicate rows are identical:

Python
dupes = docs[docs.duplicated(["Series", "Number"], keep=False)].sort_values("DocumentNo")
print(dupes[["DocumentNo", "IssueDate", "Amount"]].to_string(index=False))
identical = dupes.duplicated(["DocumentNo", "IssueDate", "Amount"], keep=False)
print("identical rows (likely an export duplicate):", bool(identical.all()))

Here the two rows for INV-2026-000143 have different dates and amounts, so they are two different documents sharing a number, which needs investigating in the source system rather than deleting.

Step 5 — Numbers issued out of order

In a sequential series, a higher number should never carry an earlier date than a lower one. A document that breaks that rule was often created later and backdated, or numbered by hand. Sort each series by number and compare every date with the latest date seen so far:

Python
seq = docs.sort_values(["Series", "Number"]).copy()
seq["LatestSoFar"] = seq.groupby("Series")["IssueDate"].cummax()
seq["PrevLatest"] = seq.groupby("Series")["LatestSoFar"].shift()
backdated = seq[seq["IssueDate"] < seq["PrevLatest"]]
print(backdated[["DocumentNo", "IssueDate", "PrevLatest"]].to_string(index=False))

Comparing with the running maximum rather than only the previous row matters when backdated documents come in a run. If invoices 301 and 302 had both been dated in March, a previous-row comparison would flag 301, then compare 302 with 301's March date and let it pass. The running maximum still holds June, so both are reported. Same-day issues are fine, as invoices 299 and 300 show; the comparison is strictly earlier.

A backdated invoice in the sequence Four consecutive invoice numbers with their issue dates and the latest date seen so far. Invoice 301 is dated 2 March while invoice 300 was dated 22 June, so 301 is earlier than the running maximum and is flagged. Invoice 302 is dated after 22 June and passes. Number IssueDate Latest before it Result 299 2026-06-22 2026-06-21 ok 300 2026-06-22 2026-06-22 ok 301 2026-03-02 2026-06-22 backdated 302 2026-06-23 2026-06-22 ok

Step 6 — The exceptions report

Put everything in one workbook: a summary per series and a line per exception, with the type of problem, so each can be explained and signed off:

Python
exceptions = pd.concat([
    pd.DataFrame([(s, r, "Missing") for s, m in unexplained.items() for r in as_ranges(m)],
                 columns=["Series", "Numbers", "Issue"]),
    dupes.drop_duplicates("DocumentNo").assign(Numbers=lambda d: d["Number"].astype(str), Issue="Duplicate")
         [["Series", "Numbers", "Issue"]],
    backdated.assign(Numbers=lambda d: d["Number"].astype(str),
                     Issue=lambda d: "Dated " + d["IssueDate"].dt.strftime("%Y-%m-%d") + ", before an earlier number")
             [["Series", "Numbers", "Issue"]],
], ignore_index=True)
exceptions["Explanation"] = ""

summary = docs.groupby("Series").agg(Documents=("Number", "size"), First=("Number", "min"), Last=("Number", "max"))
summary["Voided"] = [len(gaps[s]) - len(unexplained[s]) for s in summary.index]
summary["Missing"] = [len(unexplained[s]) for s in summary.index]
summary["Duplicates"] = docs[docs.duplicated(["Series", "Number"])].groupby("Series").size().reindex(summary.index, fill_value=0)

with pd.ExcelWriter("sequence_check.xlsx", engine="xlsxwriter") as xw:
    summary.to_excel(xw, sheet_name="Summary")
    exceptions.to_excel(xw, sheet_name="Exceptions", index=False)
    xw.sheets["Summary"].set_column(0, 5, 12)
    xw.sheets["Exceptions"].set_column(0, 1, 12)
    xw.sheets["Exceptions"].set_column(2, 2, 42)
    xw.sheets["Exceptions"].set_column(3, 3, 40)
print(summary.to_string())
print(exceptions.to_string(index=False))

The voided numbers are counted in the summary but not listed as exceptions, since they already have an explanation in the void register. The empty Explanation column is for the person reviewing the rest: "voided, void register ref 12", "test invoice deleted", "export error, re-run". An exceptions report with explanations is what an auditor wants to see.

Common pitfalls

  • Numbers read as floats. Read the ID column as str; 123.0 and lost leading zeros break the extraction.
  • Mixing series. Check each prefix separately, or every credit-note number looks like a gap in the invoices.
  • Assuming the first number. min() finds the first number present; if the series must start at 1, check that explicitly.
  • Deleting duplicates. Two different documents with one number is a finding, not a cleaning step.
  • Voided documents. Many systems keep voided numbers in a separate register; join it in before reporting them as missing.
  • Year-end rollover. A series that restarts each year needs the year in the series key, which the prefix here already contains.

Conclusion

Read document IDs as text, split them into a series prefix and a trailing number with str.extract, and check each series separately: missing numbers as the difference between the full range and the numbers present, collapsed into ranges; duplicate numbers, distinguishing identical export rows from genuinely different documents; and numbers whose issue date is earlier than the running maximum of lower numbers. Write a summary and an exceptions list with a column for explanations.

Frequently asked questions

How do I find missing numbers in a sequence with pandas? Build the full range from the minimum to the maximum number and take the set difference with the numbers present. Do it per series with groupby if the column mixes several prefixes.

How do I group consecutive missing numbers into ranges? Sort them, subtract each number's position in the list, and group by the result: consecutive numbers share the same value. The minimum and maximum of each group give the range.

How do I extract the number from an ID like INV-2026-000123? Use str.extract with a pattern such as ^(?P.*?)(?P\d+)$, then convert the Number column to int. The Series column keeps the prefix.

Is a gap in invoice numbers always a problem? Not always: voided or cancelled documents leave gaps in many systems. But every gap should be explained, which is why the report lists them with a column for the explanation.

More on validation & data quality

More on validation & data quality →