Excel BI - Excel Challenge 652

excel-challenges
excel-formulas
🔰 Booklet Sequence Answer Expected A 8, 12 A8 B 4 B4 C
Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 652

Challenge Description

🔰 Booklet Sequence Answer Expected A 8, 12 A8 B 4 B4 C

Solutions

library(tidyverse)
library(readxl)

path = "Excel/652 Generate Ticket Numbers.xlsx"
input = read_excel(path, range = "A1:B7")
test  = read_excel(path, range = "C1:C12")

result = input %>%
  separate_rows(Sequence, sep = ", ") %>%
  na.omit() %>%
  mutate(rn = row_number(), .by = Booklet) %>%
  arrange(rn, Booklet) %>%
  select(-rn) %>%
  unite(`Answer Expected`, Booklet, Sequence, sep = "")

all.equal(result, test)
#> [1] TRUE
  • Logic: Read the workbook ranges needed for the challenge; Derive the required intermediate columns; Parse the packed text or string structure; Aggregate or rank the data at the required grouping level.
  • Strengths: The code maps the workbook rule into a compact, reproducible pipeline.
  • Areas for Improvement: The solution assumes the workbook layout and selected ranges remain stable, so any structural change in the sheet would require small adjustments.
  • Gem: The elegant part is how little code is needed once the correct intermediate representation is chosen.
import pandas as pd

path = "652 Generate Ticket Numbers.xlsx"

df = pd.read_excel(path, usecols="A:B", nrows=7)
# test = pd.read_excel(path, usecols="C", nrows=12)
# input["Sequence"] = input["Sequence"].str.split(", ")
# result = (
#     input.explode("Sequence")
#     .dropna()
#     .assign(rn=lambda d: d.groupby("Booklet").cumcount() + 1)
#     .sort_values(["rn", "Booklet"])
#     .drop(columns="rn")
#     .assign(**{"Answer Expected": lambda d: d["Booklet"].astype(str) + d["Sequence"].astype(str)})[["Answer Expected"]]
#     .reset_index(drop=True)
# )
# print(result.equals(test)) # True

df["Sequence"] = df["Sequence"].str.split(", ")

df = df.explode("Sequence").dropna()

df["Answer"] = df["Booklet"] + df["Sequence"]

result = df["Answer"].reset_index(drop=True)

print(result)

The Python version follows the same grouped logic and keeps the transformation explicit in a dataframe pipeline.

Difficulty Level

Easy / Medium

The business rule is clear, though the workbook still needs a few transformation steps to reach the expected output.