Excel BI - Excel Challenge 818

excel-challenges

excel-formulas

🔰 Answer Expected Text Level1 Level2 Level3 1 : Rachel Hall Rachel Hall Esther Roberts 1.1 : Esther Roberts Donald Thomas

Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 818

Challenge Description

🔰 Answer Expected Text Level1 Level2 Level3 1 : Rachel Hall Rachel Hall Esther Roberts 1.1 : Esther Roberts Donald Thomas

Solutions

library(tidyverse)
library(readxl)

path = "Excel/800-899/818/818 Alignment As Per Indention.xlsx"
input = read_excel(path, range = "A2:A21")
test  = read_excel(path, range = "C2:E15")
# mistake in original table. Fix:
test$Level2[6] = "Jordan Richardson"

result = input %>%
  separate(Text, into = c("number", "name"), sep = " : ", extra = "merge") %>%
  separate(number, into = c("n1", "n2", "n3"), sep = "\\.", fill = "right", remove = FALSE) %>%
  group_by(n1) %>%
  mutate(
    Level1 = first(name[is.na(n2)]),
    Level2 = if_else(!is.na(n2) & is.na(n3), name, NA_character_),
    Level3 = if_else(!is.na(n3), name, NA_character_)
  ) %>%
  ungroup() %>%
  select(Level1, Level2, Level3) %>% 
  fill(Level1, .direction = "down") %>%
  group_by(Level1) %>%  fill(Level2, .direction = "downup") %>%  ungroup() %>%
  group_by(Level1, Level2) %>%  fill(Level3, .direction = "downup") %>%  ungroup() %>%
  distinct() %>%
  mutate(Level1 = ifelse(row_number() == 1, Level1, NA_character_), .by = Level1) %>%
  mutate(Level2 = ifelse(row_number() == 1, Level2, NA_character_), .by = Level2)

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 transformation is organized around the correct grouping level, which keeps the business logic clear.
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 key move is solving the problem at the right grain before shaping the final output.

import pandas as pd

path = "800-899/818/818 Alignment As Per Indention.xlsx"
input = pd.read_excel(path, usecols="A", skiprows=1, nrows=20)
test = pd.read_excel(path, usecols="C:E", skiprows=1, nrows=13)
test.loc[5, 'Level2'] = "Jordan Richardson"

input[['number', 'name']] = input.iloc[:, 0].str.split(' : ', n=1, expand=True)
input[['n1', 'n2', 'n3']] = input['number'].str.split('.', n=2, expand=True)
input[['n2', 'n3']] = input[['n2', 'n3']].replace('', pd.NA)

def first_non_null(x): return x.dropna().iloc[0] if not x.dropna().empty else None

input['Level1'] = input.groupby('n1')['name'].transform(lambda x: first_non_null(x[input['n2'].isna()]))
input['Level2'] = input.apply(lambda r: r['name'] if pd.notna(r['n2']) and pd.isna(r['n3']) else pd.NA, axis=1)
input['Level3'] = input.apply(lambda r: r['name'] if pd.notna(r['n3']) else pd.NA, axis=1)

result = input[['Level1', 'Level2', 'Level3']].copy()
result['Level1'] = result['Level1'].ffill()
result['Level2'] = result.groupby('Level1')['Level2'].ffill().groupby(result['Level1']).bfill()
result['Level3'] = result.groupby('Level2')['Level3'].bfill()
result = result.drop_duplicates().reset_index(drop=True)

for col in ['Level1', 'Level2']:
    result[col] = result.groupby(col)[col].transform(lambda x: [x.iloc[0]] + [pd.NA]*(len(x)-1))

print(result.fillna('').equals(test.fillna(''))) # 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.