Excel BI - Excel Challenge 830

excel-challenges

excel-formulas

🔰 Answer Expected Data1 Data2 Student Marks Result Type Class Joe Pass

Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 830

Challenge Description

🔰 Answer Expected Data1 Data2 Student Marks Result Type Class Joe Pass

Solutions

library(tidyverse)
library(readxl)

path = "Excel/800-899/830/830 Transpose.xlsx"
input = read_excel(path, range = "A2:B20")
test  = read_excel(path, range = "D2:F7")

result = input %>%
  mutate(type = ifelse(Data1 == "Type", Data2, NA_character_),
         Student = ifelse(Data1 == "Student", Data2, NA_character_)) %>%
  fill(type, Student) %>%
  filter(!(Data1 %in% c("Type", "Student"))) %>%
  select(-type) %>%
  pivot_wider(names_from = Data1, values_from = Data2) %>%
  mutate(Marks = as.numeric(Marks)) %>%
  arrange(desc(Marks), Student)

all.equal(result, test, check.attributes = FALSE)
# [1] TRUE

Logic: Read the workbook ranges needed for the challenge; Derive the required intermediate columns; Reshape the result into the workbook output format; Apply the business rule conditions explicitly.
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/830/830 Transpose.xlsx"

input = pd.read_excel(path, usecols="A:B", skiprows=1, nrows=18)
test = pd.read_excel(path, usecols="D:F", skiprows=1, nrows=5)

input['type'] = input['Data2'].where(input['Data1'] == 'Type').ffill()
input['Student'] = input['Data2'].where(input['Data1'] == 'Student').ffill()
filtered = input[~input['Data2'].isin(input['type'].dropna().tolist() + input['Student'].dropna().tolist())]
result = filtered.pivot(index='Student', columns='Data1', values='Data2').reset_index()
result['Marks'] = pd.to_numeric(result['Marks'])
result = result.sort_values(['Marks', 'Student'], ascending=[False, True]).reset_index(drop=True)

print(result.equals(test)) # True

The Python version mirrors the same workbook logic with a concise, direct implementation.

Difficulty Level

Medium

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