Crispo - Excel Challenge 45 2025

excel-challenges
weekly-exercises
Easy Sunday Excel Challenge
Published

November 9, 2025

Illustration for Crispo - Excel Challenge 45 2025

Challenge Description

Easy Sunday Excel Challenge

⭐ Problem Solution Units Values item 1 cat1

Solutions

library(tidyverse)
library(readxl)

path = "2025-11-09/Challenge 74.xlsx"
input = read_excel(path, range = "B3:F8", col_names = FALSE)
test  = read_excel(path, range = "H3:K15",  col_names = FALSE)

result = input %>%
  mutate(aspect = ifelse(str_detect(...1, "(?i)item", negate = TRUE), ...1, NA_character_)) %>%
  fill(aspect) %>%
  filter(...1 != aspect) %>%
  pivot_longer(cols = where(is.numeric), names_to = "item", values_to = "value") %>%
  select(...1, ...2, ...3 = aspect, ...4 = value) %>%
  mutate(...4 = as.character(...4)) %>%
  add_row(...1 = NA_character_,
          ...2 = NA_character_,
          ...3 = NA_character_,
          ...4 = "Values",
          .before = 1)

#provided answer is not correct.

  • Logic:

    • Reads the workbook range needed for the challenge

    • Reshapes the data to the grain required by the task

    • Builds the intermediate helper columns that drive the final answer

    • Uses direct text-pattern extraction instead of manual cleanup

  • Strengths:

    • The R solution stays compact and mirrors the workbook logic closely.

  • Areas for Improvement:

    • The code assumes the workbook layout and named ranges remain stable.

  • Gem:

    • The best part of the solution is choosing a tidy intermediate shape before producing the final answer.
import pandas as pd
import numpy as np

path = "2025-11-09/Challenge 74.xlsx"
input = pd.read_excel(path, usecols="B:F", skiprows=2, header=None, nrows=6)
test = pd.read_excel(path, usecols="H:K", skiprows=2, header=None, nrows=13)

result = (
    input
    .assign(
        aspect=lambda df: np.where(
            ~df.iloc[:, 0].str.contains("(?i)item", regex=True),
            df.iloc[:, 0],
            np.nan
        )
    )
    .ffill()
    .loc[lambda df: df.iloc[:, 0] != df["aspect"]]
    .melt(
        id_vars=[input.columns[0], input.columns[1], "aspect"],
        var_name="item",
        value_name="value"
    )
    .loc[:, [input.columns[0], input.columns[1], "aspect", "value"]]
    .assign(value=lambda df: df["value"].astype(str))
)
header = pd.DataFrame({
    result.columns[0]: [np.nan],
    result.columns[1]: [np.nan],
    result.columns[2]: [np.nan],
    result.columns[3]: ["Values"]
})
result = pd.concat([header, result], ignore_index=True)

# provided answer is not correct

  • Logic:

    • Reads the workbook range needed for the challenge

    • Reshapes the data to the grain required by the task

    • Builds the intermediate helper columns that drive the final answer

  • Strengths:

    • The Python version keeps the same rule in a direct pandas-oriented workflow.

  • Areas for Improvement:

    • As with the R version, any workbook layout change would require small adjustments.

  • Gem:

    • The implementation stays close to the stated challenge instead of adding unnecessary complexity.

Difficulty Level

This task is moderate:

  • It combines familiar Excel-style logic with at least one non-trivial reshape, grouping, or parsing step.

  • The answer depends on getting the output layout exactly right.