Excel BI - Excel Challenge 683

🔰 Find the value corresponding to numbers given in column N from the grid given of left hand side.

Illustration for Excel BI - Excel Challenge 683

Challenge Description

🔰 Find the value corresponding to numbers given in column N from the grid given of left hand side. First digit of number is in column B, second digit of number is in row 2, 3rd digit in column B, 4th digit in row 2 and this keeps repeating. Hence, you need to find the number for last 2 digits only. Second last digit can be a row or column and last digit can be column or row.

Solutions

R Solution

library(tidyverse)
library(readxl)

path = "Excel/683 Find Numbers in a Grid.xlsx"
input1 = read_excel(path, range = "C3:L12", col_names = FALSE) %>% as.matrix()
input2 = read_excel(path, range = "N2:N12")
test  = read_excel(path, range = "O2:O12") %>% mutate(Value = as.numeric(Value))

result = input2 %>%
  mutate(
    num = as.character(Number),
    n = nchar(num), 
    mod = n %% 2,
    row = as.numeric(ifelse(mod == 0, str_sub(num, n-1, n-1), str_sub(num, n, n)))+1,
    col = as.numeric(ifelse(mod == 0, str_sub(num, n, n), str_sub(num, n-1, n-1)))+1) %>%
  mutate(value =map2_dbl(row, col, ~input1[.x, .y])) %>%
  select(value)

all.equal(result, test, check.attributes = FALSE)

# [1] TRUE

R Analysis

• Logic: Read the workbook ranges needed for the challenge; Derive the required intermediate columns; Apply the business rule conditions explicitly.
• 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.

Python Solution

import pandas as pd
import numpy as np

path = "683 Find Numbers in a Grid.xlsx"
input1 = pd.read_excel(path,  usecols="C:L", skiprows=2, nrows=10, header=None).values
input2 = pd.read_excel(path, usecols="N", skiprows=1, nrows=11)
test = pd.read_excel(path, usecols="O", skiprows=1, nrows=11).assign(Value=lambda df: pd.to_numeric(df.iloc[:, 0]))

result = input2.assign(
    num=lambda df: df.iloc[:, 0].astype(str),
    n=lambda df: df['num'].str.len(),
    mod=lambda df: df['n'] % 2,
    row=lambda df: np.where(df['mod'] == 0, df['num'].str[-2:-1], df['num'].str[-1:]).astype(int) + 1,
    col=lambda df: np.where(df['mod'] == 0, df['num'].str[-1:], df['num'].str[-2:-1]).astype(int) + 1
).assign(
    Value=lambda df: [input1[row-1, col-1] for row, col in zip(df['row'], df['col'])]
).loc[:, ['Value']]

print(result.equals(test))

Python

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

Difficulty Level

Easy / Medium

The business rule is clear, though the workbook still needs a few transformation steps to reach the expected output.

Related Challenges

• Excel Challenge 684
• Excel Challenge 685
• Excel Challenge 686