Excel BI - Excel Challenge 751

excel-challenges

excel-formulas

🔰 List employees and all possible levels of managers.

Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 751

Challenge Description

🔰 List employees and all possible levels of managers. You will need to work out how many levels are required.

Solutions

library(tidyverse)
library(readxl)
library(igraph)

path = "Excel/700-799/751/751 Levels of Managers.xlsx"
input = read_excel(path, range = "A2:B11")
test  = read_excel(path, range = "D2:H11")

g = graph_from_data_frame(input %>% filter(!is.na(Manager)))

hierarchy <- tibble(Employee = V(g)$name) %>%
  mutate(
    Managers = map(Employee, ~
                     names(subcomponent(g, .x, mode = "out")) %>%
                     setdiff(.x)
    )
  )

result = hierarchy %>%
  unnest_wider(Managers, names_sep = "_") %>%
  arrange(Employee)
colnames(result) <- colnames(test)

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

Logic: Read the workbook ranges needed for the challenge; Derive the required intermediate columns.
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/751/751 Levels of Managers.xlsx"
input = pd.read_excel(path, usecols="A:B", skiprows=1, nrows=10)
test = pd.read_excel(path, usecols="D:H", skiprows=1, nrows=10).rename(columns=lambda col: col.split('.')[0] if '.' in col else col)

mgr_map = input.set_index('Employee')['Manager'].to_dict()

def get_manager_chain(emp):
    chain = []
    while True:
        mgr = mgr_map.get(emp)
        if mgr is None:
            break
        chain.append(mgr)
        emp = mgr
    return chain

hierarchy = pd.DataFrame({
    'Employee': input['Employee'],
    'Managers': input['Employee'].apply(get_manager_chain)
})
result = (hierarchy.explode('Managers')
          .assign(RowNumber=lambda df: df.groupby('Employee').cumcount() + 1)
          .query("RowNumber == 1 or Managers.notna()")
          .pivot(index='Employee', columns='RowNumber', values='Managers')
          .reset_index())
result.columns = ['Employee'] + [f'L{col} Manager' for col in result.columns[1:]]

print(result.equals(test)) # True

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.