Excel BI - Excel Challenge 874

excel-challenges

excel-formulas

🔰 Work out Groups Count and Longest Groups of consecutive characters.

Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 874

Challenge Description

🔰 Work out Groups Count and Longest Groups of consecutive characters. Groups Count: Calculate the number of consecutive characters groups that can be formed from the letters in the string. Each characters group needs at least 2 of the same consecutive letter.

Solutions

library(tidyverse)
library(readxl)

path <- "Excel/800-899/874/874 Consecutive Characters Groups.xlsx"
input <- read_excel(path, range = "A2:A44")
test <- read_excel(path, range = "B2:C44")

result = input %>%
  mutate(
    rn = row_number(),
    groups = map(str_split(Data, ""), rle),
    valid = map(groups, ~ tibble(letter = .$values, count = .$lengths))
  ) %>%
  unnest(valid) %>%
  filter(count >= 2) %>%
  mutate(group = str_dup(letter, count), n_groups = n(), .by = c(rn, Data)) %>%
  filter(count == max(count), .by = c(rn, Data)) %>%
  summarise(
    `Longest Group` = paste0(group, collapse = ", "),
    `Number of Groups` = first(n_groups),
    .by = c(rn, Data)
  )

r1 = input %>%
  mutate(rn = row_number()) %>%
  left_join(result, by = c("Data" = "Data", "rn" = "rn")) %>%
  replace_na(list(`Longest Group` = NA_character_, `Number of Groups` = 0)) %>%
  select(`Number of Groups`, `Longest Group`)

all.equal(r1, test, check.attributes = FALSE)
# [1] 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 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
import numpy as np
from itertools import groupby

df = pd.read_excel("Excel/800-899/874/874 Consecutive Characters Groups.xlsx", usecols=[0,1,2], skiprows=1, nrows=43, names=["Data","Number of Groups","Longest Group"])
test = df[["Number of Groups","Longest Group"]]
test.replace({np.nan: ""}, inplace=True)

def proc(s):
    if pd.isna(s): return 0, np.nan
    g = [(k, sum(1 for _ in grp)) for k, grp in groupby(s)]
    v = [(l, c) for l, c in g if c >= 2]
    if not v: return 0, np.nan
    m = max(c for _, c in v)
    return len(v), ", ".join(l*c for l, c in v if c == m)

out = df["Data"].apply(lambda x: pd.Series(proc(x)))
out.columns = ["Number of Groups","Longest Group"]
out = out.replace({np.nan: ""})

all(out == test)

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.