Excel BI - Excel Challenge 755

excel-challenges

excel-formulas

🔰 List an alphabet if it gets repeated and list the cities beneath them where these alphabets get repeated.

Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 755

Challenge Description

🔰 List an alphabet if it gets repeated and list the cities beneath them where these alphabets get repeated. Sort the header as well as entries beneath them.

Solutions

library(tidyverse)
library(readxl)

path = "Excel/700-799/755/755 Character more than once in a string.xlsx"
input = read_excel(path, range = "A2:A10")
test  = read_excel(path, range = "C2:G5")

result = input %>%
  mutate(char = str_split(str_to_lower(Cities), "")) %>%
  unnest(char) %>%
  mutate(rep_char = n(), .by = c(Cities, char)) %>%
  filter(rep_char > 1) %>%
  unique() %>%
  arrange(char, Cities) %>%
  select(-rep_char) %>%
  mutate(rn = row_number(), .by = char) %>%
  pivot_wider(names_from = char, values_from = Cities) %>%
  select(-rn)

all.equal(result, test)
# > 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 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 = "700-799/755/755 Character more than once in a string.xlsx"
input = pd.read_excel(path, usecols="A", skiprows=1, nrows=9)
test = pd.read_excel(path, usecols="C:G", skiprows=1, nrows=3)

input.columns = ['Cities']
df = (
    input.assign(char=lambda d: d['Cities'].str.lower().str.split(''))
    .explode('char')
    .query("char != ''")
)
df = df[df.duplicated(['Cities', 'char'], keep=False)]
df = df.drop_duplicates(['Cities', 'char']).sort_values(['char', 'Cities'])
df['rn'] = df.groupby('char').cumcount() + 1
result = df.pivot(index='rn', columns='char', values='Cities').reset_index(drop=True)
result.columns.name = None

print(result.equals(test))

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.