Excel BI - Excel Challenge 859

excel-challenges
excel-formulas
🔰 Extract numbers and align along with Data1 as shown.
Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 859

Challenge Description

🔰 Extract numbers and align along with Data1 as shown.

Solutions

library(tidyverse)
library(readxl)

path <- "Excel/800-899/859/859 Extract Numbers and Align.xlsx"
input <- read_excel(path, range = "A1:B6")
test <- read_excel(path, range = "C1:C12")

result = input %>%
  separate_longer_delim(Data2, delim = ", ") %>%
  mutate(rn = row_number(), .by = Data1) %>%
  arrange(rn, Data1) %>%
  select(-rn) %>%
  na.omit() %>%
  unite("Answer Expected", Data1, Data2, sep = "")

all.equal(result$`Answer Expected`, test$`Answer Expected`)
  • 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
import numpy as np

excel_path = "Excel/800-899/859/859 Extract Numbers and Align.xlsx"
input_df = pd.read_excel(excel_path, usecols="A:B", nrows=6)
test_df = pd.read_excel(excel_path, usecols="C", nrows=12)

input_long = (
    input_df.assign(Data2=input_df["Data2"].str.split(", "))
    .explode("Data2")
    .dropna()
)
input_long["rn"] = input_long.groupby("Data1").cumcount() + 1
input_long = input_long.sort_values(["rn", "Data1"]).reset_index(drop=True)
input_long["Answer Expected"] = input_long["Data1"].astype(str) + input_long["Data2"].astype(str)

print(input_long[['Answer Expected']].equals(test_df)) # True

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.