Excel BI - Excel Challenge 680

excel-challenges

excel-formulas

🔰 Team First Name1 Last Name1 First Name2 Last Name2 First Name3 Last Name3 Mars Rachel Hall

Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 680

Challenge Description

🔰 Team First Name1 Last Name1 First Name2 Last Name2 First Name3 Last Name3 Mars Rachel Hall

Solutions

library(tidyverse)
library(readxl)

path = "Excel/680 Team Alignment.xlsx"
input = read_excel(path, range = "A1:G7")
test  = read_excel(path, range = "A13:C20")

result = input %>%
  pivot_longer(cols = -c(1), names_to = c(".value", "name"), names_sep = " ") %>%
  select(-name) %>%
  na.omit() %>%
  unite("name", c("First", "Last"), sep = " ")  %>% 
  arrange(name) %>%
  mutate(rn = row_number(), .by = Team) %>%
  pivot_wider(names_from = "Team", values_from = "name") %>%
  select(Mars, Jupiter, Saturn)

all.equal(result, test)
#> [1] TRUE

Logic: Read the workbook ranges needed for the challenge; Derive the required intermediate columns; Aggregate or rank the data at the required grouping level; Reshape the result into the workbook output format.
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 = "680 Team Alignment.xlsx"

input_data = pd.read_excel(path, usecols="A:G", nrows=7)
test_data = pd.read_excel(path, usecols="A:C", skiprows=12, nrows=8)

grouped_columns = {digit: [col for col in input_data.columns if col.endswith(digit)] 
                   for digit in set(col[-1] for col in input_data.columns if col[-1].isdigit())}
for digit, cols in grouped_columns.items():
    if len(cols) > 1:
        input_data[f"united_{digit}"] = input_data[cols].agg(' '.join, axis=1).str.strip()
        input_data.drop(columns=cols, inplace=True)

input_data = (input_data.melt(id_vars=[col for col in input_data.columns if not col.startswith("united_")],
                              value_vars=[col for col in input_data.columns if col.startswith("united_")],
                              value_name="united_value")
              .dropna(subset=["united_value"])
              .query("united_value.str.strip() != ''", engine='python')
              .sort_values(by=["Team", "united_value"])
              .assign(row_number=lambda df: df.groupby("Team").cumcount() + 1)
              .pivot(index="row_number", columns="Team", values="united_value")
              .reset_index(drop=True)[["Mars", "Jupiter", "Saturn"]])

print(input_data.equals(test_data)) # 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.