Excel BI - Excel Challenge 732

excel-challenges

excel-formulas

🔰 Answer Expected Data Alphabet Value1 Value2 Value3 X-42, Y-53 A 84 56

Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 732

Challenge Description

🔰 Answer Expected Data Alphabet Value1 Value2 Value3 X-42, Y-53 A 84 56

Solutions

library(tidyverse)
library(readxl)

path = "Excel/700-799/732/732.xlsx"
input = read_excel(path, range = "A2:A6")
test = read_excel(path, range = "C2:F7")

result = input %>%
  separate_rows(Data, sep = ", ") %>%
  separate(Data, into = c("Alphabet", "Value"), sep = "-") %>%
  mutate(rn = row_number(), .by = Alphabet) %>%
  arrange(Alphabet, rn) %>%
  pivot_wider(names_from = rn, values_from = Value, names_prefix = "Value")

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 reshaping step mirrors the workbook output closely instead of forcing extra post-processing.
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 last reshape turns a raw transformation into something that already looks like a report.

import pandas as pd

path = "700-799/732/732.xlsx"

input = pd.read_excel(path, usecols="A", skiprows=1, nrows=5, names=["Data"])
test = pd.read_excel(path, usecols="C:F", skiprows=1, nrows=6)
test['Value1'] = test['Value1'].astype('float64')

input_expanded = input['Data'].str.split(', ', expand=True).stack().reset_index(level=1, drop=True).to_frame('Data')
input_expanded.reset_index(drop=True, inplace=True)

input_expanded[['Alphabet', 'Value']] = input_expanded['Data'].str.split('-', expand=True)
input_expanded['Value'] = input_expanded['Value'].astype('int64')
input_expanded.drop(columns='Data', inplace=True)

input_expanded['rn'] = input_expanded.groupby('Alphabet').cumcount() + 1

result = input_expanded.pivot_table(index='Alphabet', columns='rn', values='Value', aggfunc='first')
result.columns = [f'Value{col}' for col in result.columns]
result = result.reset_index()

print(result.equals(test)) # True

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

Difficulty Level

Medium

The individual steps are manageable, but the correct transformation pattern is not obvious from the raw data.