Excel BI - Excel Challenge 772

excel-challenges

excel-formulas

🔰 Generate the sum of numbers against alphabets.

Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 772

Challenge Description

🔰 Generate the sum of numbers against alphabets. If an alphabet doesn’t have any number (case of D here), that should be skipped.

Solutions

library(tidyverse)
library(readxl)

path = "Excel/700-799/772/772 Split and Sum.xlsx"
input = read_excel(path, range = "A1:A14")
test  = read_excel(path, range = "B2:C5", col_names = c("Letter", "Value"))

result = input %>%
  separate_wider_delim(Data, delim = "-", names = c("Letter", "Number"), too_few = "align_start") %>%
  na.omit() %>%
  summarise(Value = sum(as.numeric(Number), na.rm = T), .by = Letter) 

all.equal(result, test)
# mistake in given solution

Logic: Read the workbook ranges needed for the challenge; 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 = "700-799/772/772 Split and Sum.xlsx"
input = pd.read_excel(path, usecols="A", nrows=14, header=0)
test = pd.read_excel(path, usecols="B:C", skiprows=1, nrows=4, names=["Letter", "Value"])

split = (
    input['Data']
    .str.split('-', n=1, expand=True)
    .dropna()
    .set_axis(['Letter', 'Number'], axis=1)
    .assign(Number=lambda df: pd.to_numeric(df['Number'], errors='coerce'))
)
result = (
    split
    .groupby('Letter', as_index=False)['Number']
    .sum()
    .rename(columns={'Number': 'Value'})
)

comparison = result.equals(test)
# Mistake in given solution.

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.