Excel BI - Excel Challenge 720

excel-challenges

excel-formulas

🔰 Transpose the table as shown.

Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 720

Challenge Description

🔰 Transpose the table as shown. List only those rivers which contain at least 2 different vowels.

Solutions

library(tidyverse)
library(readxl)
library(stringi)

path = "Excel/700-799/720/720 Transpose if minimum 2 different vowels.xlsx"
input = read_excel(path, range = "A2:B16")
test = read_excel(path, range = "D2:I5")

result = input %>%
  mutate(Rivers = stri_trans_general(Rivers, "Latin-ASCII")) %>%
  rowwise() %>%
  mutate(
    Rivers_vows = list(unique(unlist(str_extract_all(
      str_to_lower(Rivers),
      "[aeiou]"
    ))))
  ) %>%
  filter(length(Rivers_vows) >= 2) %>%
  ungroup() %>%
  select(-Rivers_vows) %>%
  arrange(Group, Rivers) %>%
  mutate(rn = row_number(), .by = Group) %>%
  pivot_wider(names_from = rn, values_from = Rivers, names_prefix = "Rivers")

all.equal(test, result, check.attributes = FALSE, check.names = FALSE)
# 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 solution stays close to the text pattern itself, which makes the extraction logic easy to audit.
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: A small number of well-targeted text patterns does most of the heavy lifting.

import pandas as pd
import unicodedata
import re

path = "700-799/720/720 Transpose if minimum 2 different vowels.xlsx"
input = pd.read_excel(path, usecols="A:B", skiprows=1, nrows=15)
test = pd.read_excel(path, usecols="D:I", skiprows=1, nrows=3).rename(columns=lambda col: col.replace('.1', ''))

latin_ascii = lambda s: unicodedata.normalize('NFKD', str(s)).encode('ascii', 'ignore').decode()
unique_vowels = lambda s: set(re.findall(r'[aeiou]', s.lower()))

df = input.copy()
df['Rivers'] = df['Rivers'].map(latin_ascii)
df = df[df['Rivers'].map(lambda s: len(unique_vowels(s)) >= 2)]
df = df.sort_values(['Group', 'Rivers'])
df['rn'] = df.groupby('Group').cumcount() + 1
result = df.pivot(index='Group', columns='rn', values='Rivers').add_prefix('River').reset_index()
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.