Excel BI - Excel Challenge 814

excel-challenges

excel-formulas

🔰 Extract the sequence of alphabets where next alphabet > previous alphabet.

Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 814

Challenge Description

🔰 Extract the sequence of alphabets where next alphabet > previous alphabet.

Solutions

library(tidyverse)
library(readxl)

path = "Excel/800-899/814/814 Increasing Alphabets.xlsx"
input = read_excel(path, range = "A1:A10")
test  = read_excel(path, range = "B1:B10") %>% replace_na(list(`Answer Expected` = ""))

split_increasing = function(s) {
  chars = strsplit(s, "")[[1]]
  idx = cumsum(c(FALSE, diff(utf8ToInt(s)) <= 0))
  tibble(chars, idx) %>%
    group_by(idx) %>%
    summarise(substr = paste0(chars, collapse = ""), .groups = "drop") %>%
    pull(substr) %>%
    discard(~ nchar(.x) < 2) %>%
    paste(collapse = ", ")
}

result = input %>%
  mutate(`Answer Expected` = map_chr(Data, split_increasing)) 

all.equal(result$`Answer Expected`, test$`Answer Expected`)  
# [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 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 = "800-899/814/814 Increasing Alphabets.xlsx"
input = pd.read_excel(path, usecols="A", nrows=10)
test = pd.read_excel(path, usecols="B", nrows=10).fillna({"Answer Expected": ""})

def split_increasing(s):
    chars = list(str(s))
    idx = [0]
    for i in range(1, len(chars)):
        idx.append(idx[-1] + (ord(chars[i]) <= ord(chars[i-1])))
    groups = {}
    for i, g in enumerate(idx):
        groups.setdefault(g, []).append(chars[i])
    return ', '.join(''.join(g) for g in groups.values() if len(g) >= 2)

input['Answer Expected'] = input.iloc[:,0].apply(split_increasing)
print(input["Answer Expected"].equals(test["Answer Expected"])) # TRUE

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.