Excel BI - Excel Challenge 915

excel-challenges

excel-formulas

🔰 915 Missing Invoice Numbers.xlsx says: > Find the missing invoice numbers for each series.

Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 915

Challenge Description

🔰 The prompt in 915 Missing Invoice Numbers.xlsx says: Find the missing invoice numbers for each series. The invoice IDs are strings like: text ABC-101 ABC-102 ABC-104 The goal is to detect which numbers are missing within each prefix series and return the absent invoice IDs.

Solutions

library(tidyverse)
library(readxl)

path <- "Excel/900-999/915/915 Missing Invoice Numbers.xlsx"
input <- read_excel(path, range = "A1:A24")
test <- read_excel(path, range = "B1:B13") %>%
  arrange(`Answer Expected`)

result = input %>%
  separate(
    Invoice_ID,
    into = c("prefix", "number"),
    sep = "-",
    remove = FALSE,
    convert = TRUE
  ) %>%
  group_by(prefix) %>%
  complete(number = full_seq(number, 1)) %>%
  ungroup() %>%
  filter(is.na(Invoice_ID)) %>%
  unite("Invoice_ID", prefix, number, sep = "-")

all.equal(result$Invoice_ID, test$`Answer Expected`)
# [1] TRUE

Logic: Split invoice IDs into prefix and numeric part.; Group rows by prefix.; Generate the full expected numeric sequence within each group..
Strengths: The core idea is sequence completion.
Areas for Improvement: The approach assumes the workbook structure and naming conventions stay stable, so any changed input shape would need minor adjustments.
Gem: You do not find missing invoice numbers by pairwise comparison.

import pandas as pd

path = "Excel/900-999/915/915 Missing Invoice Numbers.xlsx"
input = pd.read_excel(path, usecols="A", nrows=24)
test = pd.read_excel(path, usecols="B", nrows=12).sort_values(by="Answer Expected").reset_index(drop=True)

input[['prefix', 'number']] = input['Invoice_ID'].str.split('-', expand=True)
input['number'] = input['number'].astype(int)

full = (
    input.groupby("prefix")["number"]
    .apply(lambda x: pd.Series(range(x.min(), x.max() + 1)))
    .reset_index()
    .rename(columns={"level_1": "idx", 0: "number"})
    .drop(columns="idx")
)

result = (
    full.merge(input, on=["prefix", "number"], how="left")
    .loc[lambda x: x["Invoice_ID"].isna(), ["prefix", "number"]]
    .assign(Invoice_ID=lambda x: x["prefix"] + "-" + x["number"].astype(str))
    [["Invoice_ID"]]
    .reset_index(drop=True)
)

print(result["Invoice_ID"].equals(test["Answer Expected"]))

The Python version follows the same structure: split invoice ids into prefix and numeric part.; convert the numeric part into integers..

Difficulty Level

Easy

Once the core pattern is recognized, the implementation is short and direct.