Excel BI - Excel Challenge 872

excel-challenges

excel-formulas

🔰 Data Answer Expected apples applEs banana banAna cherry date fruit datE fruIt elderberry

Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 872

Challenge Description

🔰 Data Answer Expected apples applEs banana banAna cherry date fruit datE fruIt elderberry

Solutions

library(tidyverse)
library(readxl)

path <- "Excel/800-899/872/872 Make Alternate Vowel Uppercase.xlsx"
input <- read_excel(path, range = "A1:A50")
test <- read_excel(path, range = "B1:B50")

result = input %>%
  mutate(rn = row_number()) %>%
  mutate(chars = str_split(Data, "")) %>%
  unnest_longer(chars) %>%
  mutate(
    is_vowel = chars %in% c("a", "e", "i", "o", "u", "A", "E", "I", "O", "U")
  ) %>%
  group_by(rn) %>%
  mutate(vowel_count = cumsum(is_vowel)) %>%
  mutate(
    chars = if_else(is_vowel & vowel_count %% 2 == 0, toupper(chars), chars)
  ) %>%
  summarise(Data = str_c(chars, collapse = ""))

all(result$Data == test$`Answer Expected`)

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 transformation is organized around the correct grouping level, which keeps the business logic clear.
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 key move is solving the problem at the right grain before shaping the final output.

import pandas as pd

path = "Excel/800-899/872/872 Make Alternate Vowel Uppercase.xlsx"
input = pd.read_excel(path, usecols="A", nrows=50)
test = pd.read_excel(path, usecols="B", nrows=50)

def alternate_vowel_uppercase(s):
    vowels = "aeiouAEIOU"
    count = 0
    res = []
    for c in s:
        if c in vowels:
            count += 1
            res.append(c.upper() if count % 2 == 0 else c)
        else:
            res.append(c)
    return "".join(res)

result = input.iloc[:, 0].apply(alternate_vowel_uppercase)

print(result.tolist() == test.iloc[:, 0].tolist())

The Python version keeps the algorithm explicit, which helps when the challenge depends on a greedy or iterative rule.

Difficulty Level

Medium

The individual steps are manageable, but the correct transformation pattern is not obvious from the raw data.