Excel BI - Excel Challenge 728

excel-challenges

excel-formulas

🔰 For each name in Name1 column, list the names from Name2 where either first name matches with first name of Name1 or last name matches with last name of Name1.

Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 728

Challenge Description

🔰 For each name in Name1 column, list the names from Name2 where either first name matches with first name of Name1 or last name matches with last name of Name1. If a name from Name2 is already used, omit that name in succeeding results. For ex. for Zachary W. Cox, Linda Cox was already used, hence only Amanda Cox is a match.

Solutions

library(tidyverse)
library(readxl)

path = "Excel/700-799/728/728 Align Names.xlsx"
input = read_excel(path, range = "A2:B11")
test = read_excel(path, range = "D2:E6")

used = character(0)
results = list()

for (n1 in input$`Name 1`) {
  first = word(n1, 1)
  last = word(n1, -1)

  matches = input$`Name 2` |>
    setdiff(used) |>
    keep(~ word(.x, 1) == first || word(.x, -1) == last)

  if (length(matches) > 0) {
    used = union(used, matches)
    results[[n1]] = paste(sort(matches), collapse = ", ")
  }
}

result = tibble(
  name_1 = names(results),
  name_2 = unlist(results)
)

Logic: Read the workbook ranges needed for the challenge; Iterate through the sequence until the rule is satisfied.
Strengths: The algorithm is explicit about the sequence rule, so the control flow is easy to validate against the prompt.
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 non-obvious part is the local rule inside the loop, because that rule determines the whole output.

import pandas as pd

path = "700-799/728/728 Align Names.xlsx"
input_df = pd.read_excel(path, usecols="A:B", skiprows=1, nrows=10)
test_df = pd.read_excel(path, usecols="D:E", skiprows=1, nrows=5)

used = set()
results = {}

def first_word(s):
    return str(s).split()[0] if pd.notnull(s) else ""

def last_word(s):
    return str(s).split()[-1] if pd.notnull(s) else ""

for n1 in input_df['Name 1']:
    first = first_word(n1)
    last = last_word(n1)
    matches = [
        n2 for n2 in input_df['Name 2']
        if n2 not in used and (first_word(n2) == first or last_word(n2) == last)
    ]
    if matches:
        used.update(matches)
        results[n1] = ", ".join(sorted(map(str, matches)))

result = pd.DataFrame({
    "name_1": list(results.keys()),
    "name_2": list(results.values())
})
print(result)

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.