Excel BI - Excel Challenge 761

excel-challenges

excel-formulas

🔰 List the open POs.

Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 761

Challenge Description

🔰 List the open POs.

Solutions

library(tidyverse)
library(readxl)

path = "Excel/700-799/761/761 Open POs.xlsx"
input = read_excel(path, range = "A1:B11")
test  = read_excel(path, range = "D1:D7")

expand_po = function(df) {
  df %>%
    separate(PO, into = c("Min", "Max"), sep = "-", fill = "right") %>%
    mutate(PO = map2(as.numeric(Min), as.numeric(coalesce(Max, Min)), seq)) %>%
    select(Status, PO) %>%
    unnest(PO)
}

opens  = input  %>% filter(Status == "Open")   %>% expand_po()
closed = input  %>% filter(Status == "Closed") %>% expand_po()

still_open = opens %>%
  anti_join(closed, by = "PO") %>%
  group_by(grp = cumsum(c(1, diff(PO) > 1))) %>%
  summarise(`Answer Expected` = ifelse(n() == 1, as.character(min(PO)), paste0(min(PO), "-", max(PO))), .groups = "drop") %>%
  select(`Answer Expected`)

all.equal(still_open, 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 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 = "700-799/761/761 Open POs.xlsx"
input = pd.read_excel(path, usecols="A:B", nrows=11)
test = pd.read_excel(path, usecols="D", nrows=6)

def expand_sequence(notation):
    parts = notation.split('-')
    return list(range(int(parts[0]), int(parts[-1]) + 1)) if len(parts) > 1 else [int(parts[0])]

def reverse_expand_sequence(seq):
    return str(seq[0]) if len(seq) == 1 else f"{seq[0]}-{seq[-1]}"


input['PO'] = input['PO'].astype(str).apply(expand_sequence)
input = input.explode('PO')
opens = input[input['Status'] == 'Open']
closed = input[input['Status'] == 'Closed']
opens_still = opens[~opens['PO'].isin(closed['PO'])]
opens_still['grp'] = (opens_still['PO'].diff().fillna(1) > 1).cumsum()
result = opens_still.groupby('grp')['PO'].apply(lambda x: reverse_expand_sequence(sorted(x))).reset_index(drop=True)

print(result.equals(test['Answer Expected']))

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.