Excel BI - Excel Challenge 804

excel-challenges

excel-formulas

🔰 Create the group of cities sorted in alphabetical order.

Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 804

Challenge Description

🔰 Create the group of cities sorted in alphabetical order. Then sort the names alphabetically.

Solutions

library(tidyverse)
library(readxl)

path = "Excel/800-899/804/804 Sort Cities Names.xlsx"
input = read_excel(path, range = "A2:B21")
test  = read_excel(path, range = "D2:E21")

result = input %>%
  arrange(City, Names) %>%
  mutate(rn = row_number(), .by = City) %>%
  arrange(rn, City) %>%
  select(-rn)

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.
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 = "800-899/804/804 Sort Cities Names.xlsx"
input = pd.read_excel(path, usecols="A:B", skiprows=1, nrows=20)
test = pd.read_excel(path, usecols="D:E", skiprows=1, nrows=20).rename(columns=lambda c: c.replace('.1', ''))

result = (
    input
    .sort_values(['City', 'Names'])
    .assign(rn=lambda x: x.groupby('City').cumcount()+1)
    .sort_values(['rn', 'City'])
    .drop(columns='rn')
    .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

Easy / Medium

The business rule is clear, though the workbook still needs a few transformation steps to reach the expected output.