Excel BI - Excel Challenge 825

excel-challenges
excel-formulas
🔰 Answer Expected ID Planet River Subject Entity Type Jupiter Volga, Nile Planet-1
Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 825

Challenge Description

🔰 Answer Expected ID Planet River Subject Entity Type Jupiter Volga, Nile Planet-1

Solutions

library(tidyverse)
library(readxl)

path = "Excel/800-899/825/825 Unpivot.xlsx"
input = read_excel(path, range = "A2:D5")
test  = read_excel(path, range = "F2:H15")

result = input %>%
  pivot_longer(-ID, names_to = "Type", values_to = "Entity") %>%
  separate_longer_delim(Entity, delim = ", ") %>%
  arrange(ID, Type, Entity) %>%
  na.omit() %>%
  mutate(Type = paste0(Type,"-", row_number()), .by = Type) %>%
  select(ID, Entity, Type) %>%
  ungroup()

all.equal(result, test, check.attributes = FALSE)
# [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 = "800-899/825/825 Unpivot.xlsx"
input = pd.read_excel(path, sheet_name=0, usecols="A:D", skiprows=1, nrows=3)
test = pd.read_excel(path, sheet_name=0, usecols="F:H", skiprows=1, nrows=14).rename(columns=lambda x: x.replace('.1', ''))

result = input.melt(id_vars='ID', var_name='Type', value_name='Entity')
result = result.dropna()
result = result.assign(Entity=result['Entity'].str.split(', ')).explode('Entity')
result = result.sort_values(['ID', 'Type', 'Entity'])
result['Type'] += '-' + (result.groupby('Type').cumcount() + 1).astype(str)
result = result[['ID', 'Entity', 'Type']].reset_index(drop=True)

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.