Given 10×10 grid in A2:K11 contains bird names across rows (not in columns). Column M contains few bird names. Find which bird names in column M appear in the grid. Replace all other letters with x in the grid except for the found bird names. Highlighting has been done to show where the bird names are found. You need not take care of highlighting part. Not all bird names are there.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 443
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Find Birds in Grid Rows with Power Query
Power Query solution 1 for Find Birds in Grid Rows, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "grid"]}[Content],
Birds = Excel.CurrentWorkbook(){[Name = "birds"]}[Content][Birds],
ToRow = Table.ToRows(Source),
Combine = List.Transform(
ToRow,
each [
C = Text.Combine(_),
R = List.Accumulate(Birds, C, (x, y) => Text.Replace(x, y, Text.Repeat("1", Text.Length(y)))),
L = Text.ToList(R),
Z = List.Zip({_, L}),
O = List.Transform(Z, (f) => if f{1} = "1" then f{0} else "x")
][O]
),
Return = Table.FromRows(Combine)
in
ReturnPower Query solution 2 for Find Birds in Grid Rows, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Birds = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
Col = Table.Combine(
Table.AddColumn(
Source,
"A",
each
let
a = Text.Combine(Record.ToList(_)),
b = List.Transform(Birds[Birds], each Text.PositionOf(a, _)),
c =
if List.Distinct(b) = {- 1} then
Text.Repeat("x", Text.Length(a))
else
let
A = List.Select(b, each _ <> - 1){0},
B = List.Select(Birds[Birds], each Text.PositionOf(a, _) > - 1){0},
C = Text.Repeat("x", A) & B & Text.Repeat("x", Text.Length(a) - A - Text.Length(B))
in
C,
d = Text.ToList(c)
in
Table.FromRows({d})
)[A]
)
in
ColPower Query solution 3 for Find Birds in Grid Rows, proposed by Alexis Olson:
let
Source = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
ColCount = Table.ColumnCount(Source),
Birds = {"crane", "dumbledore", "hawk", "magpie", "peacock", "seagull", "swan"},
AddRowCol = Table.AddColumn(Source, "Row", each Text.Combine(Record.ToList(_))),
Match = Table.AddColumn(
AddRowCol,
"Match",
(row) => List.First(List.Select(Birds, each Text.Contains(row[Row], _)))
),
Position = Table.AddColumn(
Match,
"Position",
each try Text.PositionOf([Row], [Match]) otherwise ColCount
),
Output = Table.AddColumn(
Position,
"Output",
each Text.PadEnd(Text.Repeat("x", [Position]) & [Match] ?? "", ColCount, "x")
),
Result = Table.FromRows(List.Transform(Output[Output], Text.ToList))
in
ResultPower Query solution 4 for Find Birds in Grid Rows, proposed by Mihai Radu O:
let
tbl2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
tbl1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
sol =
let
a = Text.Combine(
Table.CombineColumns(
tbl1,
Table.ColumnNames(tbl1),
Combiner.CombineTextByDelimiter(""),
"unite"
)[unite]
),
b = tbl2[Birds],
c = List.Transform(b, Text.Upper),
d = List.Accumulate(List.Zip({b, c}), a, (s, c) => Text.Replace(s, c{0}, c{1})),
e = List.Transform(
Text.ToList(d),
each if Character.ToNumber(_) >= 97 then "x" else Text.Lower(_)
),
f = Table.FromRows(List.Split(e, 10))
in
f
in
solPower Query solution 5 for Find Birds in Grid Rows, proposed by Glyn Willis:
let
Words = {"crane", "dumbledore", "hawk", "magpie", "peacock", "seagull", "swan"},
Source = Excel.CurrentWorkbook(){[Name = "Table3"]}[Content],
Grid = Table.FromRows(
let
tr = Record.FieldCount(Source{0}),
ph = Text.Repeat("x", tr)
in
List.Transform(
Table.ToRows(Source),
(ol) =>
let
t = Text.Combine(ol),
l = List.Select(
List.Transform(
Words,
(il) =>
let
tp = Text.PositionOf(t, il)
in
if tp > - 1 then Text.ReplaceRange(ph, tp, Text.Length(il), il) else null
),
(row) => row <> null
)
in
if List.IsEmpty(l) then Text.ToList(ph) else Text.ToList(l{0})
)
)
in
GridSolving the challenge of Find Birds in Grid Rows with Excel
Excel solution 1 for Find Birds in Grid Rows, proposed by Bo Rydobon 🇹🇭:
=LET(
g,
B2:K11,
IF(
g=MID(
REDUCE(
BYROW(
g,
CONCAT
),
M2:M8,
LAMBDA(
a,
v,
SUBSTITUTE(
a,
v,
REPT(
0,
LEN(
v
)
)
)
)
),
SEQUENCE(
,
COLUMNS(
g
)
),
1
),
"x",
g
)
)Excel solution 2 for Find Birds in Grid Rows, proposed by Rick Rothstein:
=LET(
m,
MID(
REDUCE(
UPPER(
CONCAT(
B2:K11
)
),
M2:M8,
LAMBDA(
a,
x,
SUBSTITUTE(
a,
UPPER(
x
),
x
)
)
),
SEQUENCE(
,
100
),
1
),
WRAPROWS(
IF(
CODE(
m
)<91,
"x",
m
),
10
)
)Excel solution 3 for Find Birds in Grid Rows, proposed by John V.:
=IF(
CODE(
MID(
REDUCE(
CONCAT(
B2:K11
),
M2:M8,
LAMBDA(
a,
v,
SUBSTITUTE(
a,
v,
UPPER(
v
)
)
)
),
SEQUENCE(
10,
10
),
1
)
)<91,
B2:K11,
"x"
)Excel solution 4 for Find Birds in Grid Rows, proposed by محمد حلمي:
=REDUCE(
"",
M3:M9,
LAMBDA(
a,
m,
LET(
i,
MID(
m,
IFERROR(
FIND(
B3:K12,
m
),
99
),
1
),
e,
--TOCOL(
m=SCAN(
0,
i,
LAMBDA(
a,
d,
IF(
d>"",
a&d,
""
)
)
)
),
a&IFNA(
IF(
XMATCH(
SEQUENCE(
10,
10
),
1+XMATCH(
1,
e
)-SEQUENCE(
LEN(
m
)
)
),
i
),
""
)
)
)
)Excel solution 5 for Find Birds in Grid Rows, proposed by Kris Jaganah:
=LET(
a,
DROP(
REDUCE(
"",
M2:M8,
LAMBDA(
x,
y,
HSTACK(
x,
IFERROR(
FIND(
y,
BYROW(
B2:K11,
CONCAT
)
),
0
)&y
)
)
),
,
1
),
b,
BYROW(
IF(
--LEFT(
a
)>0,
LEFT(
a
)&"-"&MID(
a,
2,
10
),
""
),
CONCAT
),
c,
TEXTBEFORE(
b,
"-",
,
,
,
0
),
d,
TEXTAFTER(
b,
"-",
,
,
,
""
),
e,
REPT(
"x",
IF(
c-1<0,
0,
c-1
)
)&d,
MID(
CONCAT(
e&REPT(
"x",
10-LEN(
e
)
)
),
SEQUENCE(
10,
10
),
1
)
)Excel solution 6 for Find Birds in Grid Rows, proposed by Julian Poeltl:
=LET(
T,
CONCAT(
B2:K11
),
No,
LEN(
T
),
Bi,
M2:M8,
BN,
REPT(
ROW(
Bi
)-1,
LEN(
Bi
)
),
RF,
SUBSTITUTE(
T,
Bi,
BN
),
N,
MID(
RF,
SEQUENCE(
,
No
),
1
)*1,
RX,
SUBSTITUTE(
CONCAT(
BYCOL(
IF(
ISNUMBER(
N
),
N,
0
),
LAMBDA(
S,
SUM(
S
)
)
)
),
0,
"x"
),
NN,
TEXTSPLIT(
RX,
BN
),
F,
TEXTSPLIT(
RX,
"x"
),
SN,
XLOOKUP(
FILTER(
F,
F<>""
),
BN,
Bi
),
C,
IFERROR(
TOROW(
VSTACK(
NN,
SN
),
1,
TRUE
),
""
),
Chain,
CONCAT(
FILTER(
C,
C<>""
)
),
WRAPROWS(
MID(
Chain,
SEQUENCE(
,
No
),
1
),
10
)
)Excel solution 7 for Find Birds in Grid Rows, proposed by Timothée BLIOT:
=TEXTSPLIT(TEXTJOIN("|",
,
BYROW(B2:K11,
LAMBDA(x,
LET(C,
CONCAT,
R,
REPT,
A,
C(MAP(M2:M8,
LAMBDA(y,
LET(B,
FIND(
y,
C(
x
)
),
IF(ISNUMBER(
B
),
R(
"x:",
B-1
)&C (REGEXEXTRACT(
y,
"w",
1
)&":")&R(
"x:",
11-LEN(
y
)-B
),
""))))),
IF(
A="",
R(
"x:",
10
),
A
))))),
":",
"|",
1)Excel solution 8 for Find Birds in Grid Rows, proposed by Hussein SATOUR:
=LET(
g,
B2:K11,
a,
REDUCE(
"",
M2:M8,
LAMBDA(
x,
y,
VSTACK(
x,
IFERROR(
SEQUENCE(
LEN(
y
),
,
FIND(
y,
CONCAT(
g
)
),
1
),
""
)
)
)
),
WRAPROWS(
IF(
IFNA(
XMATCH(
ROW(
1:100
),
a
),
0
)>0,
TOCOL(
g
),
"x"
),
10
)
)Excel solution 9 for Find Birds in Grid Rows, proposed by Oscar Mendez Roca Farell:
=LET(s,
SEQUENCE(
,
10
),
t,
TOROW(
M2:M8
),
F,
LAMBDA(
i,
BYROW(
i,
LAMBDA(
r,
MAX(
IFERROR(
r,
)
)
)
)
),
p,
FIND(
t,
BYROW(
B2:K11,
LAMBDA(
r,
CONCAT(
r
)
)
)
),
q,
IFS(
p>0,
LEN(
t
)
),
IFS((s>F(
p
)-1)*(sExcel solution 10 for Find Birds in Grid Rows, proposed by Duy Tùng:
=LET(
d,
M2:M8,
a,
CONCAT(
EXPAND(
B2:K11,
,
COLUMNS(
B2:K11
)+1,
0
)
),
b,
FIND(
d,
a
)-1,
c,
SORTBY(
d,
b
),
e,
TOCOL(
SORT(
b
),
3
),
DROP(
MID(
REDUCE(
"",
e,
LAMBDA(
x,
y,
x&REPT(
"x",
y-LEN(
x
)
)&INDEX(
c,
MATCH(
y,
e
)
)
)
),
SEQUENCE(
10,
11
),
1
),
,
-1
)
)Excel solution 11 for Find Birds in Grid Rows, proposed by Sunny Baggu:
=LET(
res,
REDUCE(
0,
M2:M8,
LAMBDA(x,
y,
x +
LET(
_arr,
BYROW(
B2:K11,
LAMBDA(
a,
CONCAT(
a
)
)
),
val,
DROP(
REDUCE(
"",
IFERROR(
SEARCH(
y,
_arr
),
10
),
LAMBDA(a,
v,
VSTACK(
a,
LET(
&_a,
EXPAND(
0,
,
v - 1,
0
),
_b,
SEQUENCE(
,
LEN(
y
),
v,
),
_c,
EXPAND(0,
,
10 - (v - 1) - LEN(
y
),
0),
_d,
EXPAND(
0,
,
v,
0
),
IF(
AND(
v = 1,
LEN(
y
) = 10
),
_b,
IF(
v = 10,
_d,
IF(
v = 1,
HSTACK(
_b,
_c
),
HSTACK(
_a,
_b,
_c
)
)
)
)
)
)
)
),
1
),
val
)
)
),
IF(
res,
B2:K11,
"x"
)
)Excel solution 12 for Find Birds in Grid Rows, proposed by LEONARD OCHEA 🇷🇴:
=LET(n,
10,
g,
B2:K11,
b,
TOROW(
M2:M8
),
c,
BYROW(
g,
CONCAT
),
p,
IFERROR(
FIND(
b,
c
),
),
i,
BYROW(
p,
SUM
),
j,
BYROW(
IF(
p,
LEN(
b
),
),
SUM
),
m,
IF(i,
(n*(SEQUENCE(
n
)-1)+i),
),
s,
SEQUENCE(
n,
n
),
IF((s>=m)*(sExcel solution 13 for Find Birds in Grid Rows, proposed by Abdallah Ally:
=LET(
a,
B2:K11,
b,
REDUCE(
CONCAT(
a
),
M2:M8,
LAMBDA(
x,
y,
SUBSTITUTE(
x,
y,
UPPER(
y
)
)
)
),
c,
MID(
b,
SEQUENCE(
LEN(
b
)
),
1
),
d,
IF(
EXACT(
c,
LOWER(
c
)
),
"x",
c
),
WRAPROWS(
LOWER(
d
),
ROWS(
a
),
""
)
)Excel solution 14 for Find Birds in Grid Rows, proposed by 🇵🇪 Ned Navarrete C.:
=MID(
CONCAT(
MAP(
BYROW(
B2:K11,
LAMBDA(
f,
CONCAT(
f
)
)
),
LAMBDA(
i,
LET(
n,
FIND(
M2:M8,
i
),
z,
ISNUMBER(
n
),
IF(
SUM(
N(
z
)
),
LET(
p,
SUM(
IFERROR(
n,
0
)
),
x,
FILTER(
M2:M8,
z
),
j,
LEN(
x
),
s,
HSTACK(
LEFT(
i,
p-1
),
MID(
i,
p,
j
),
RIGHT(
i,
11-p-j
)
),
CONCAT(
IF(
s=x,
s,
REPT(
"x",
LEN(
s
)
)
)
)
),
REPT(
"x",
10
)
)
)
)
)
),
SEQUENCE(
10,
10
),
1
)Excel solution 15 for Find Birds in Grid Rows, proposed by Andy Heybruch:
=TEXTSPLIT(
TEXTJOIN(
"|",
,
BYROW(
BYROW(
B2:K11,
LAMBDA(
a,
CONCAT(
a
)
)
),
LAMBDA(
_r,
LET(
_bird,
$M$2:$M$8,
_chk,
IFERROR(
TAKE(
SORT(
SEARCH(
_bird,
_r
)
),
1
),
1
),
_match,
IFERROR(
FILTER(
_bird,
IFERROR(
SEARCH(
_bird,
_r
),
0
)>0
),
""
),
_output,
REPT(
"x",
_chk-1
)&_match&REPT(
"x",
11-_chk-LEN(
_match
)
),
TEXTJOIN(
",",
,
MID(
_output,
SEQUENCE(
10
),
1
)
)
)
)
)
),
",",
"|"
)Excel solution 16 for Find Birds in Grid Rows, proposed by Ziad A.:
=BYROW(
B2:K11,
LAMBDA(
r,
SPLIT(
REGEXREPLACE(
LEFT(
REGEXREPLACE(
JOIN(
,
r
),
"("&JOIN(
"|",
M2:M8
)&")|.",
"$1x"
),
COUNTA(
r
)
),
,
"0"
),
0
)
)
)Excel solution 17 for Find Birds in Grid Rows, proposed by Sandeep Marwal:
=LET(
bird,
$M$2:$M$8,
grid,
$B$2:$K$11,
gridwords,
BYROW(
grid,
LAMBDA(
a,
TEXTJOIN(
"",
,
a
)
)
),
birdfound,
MAP(gridwords,
LAMBDA(a,
IFERROR(INDEX(bird,
XMATCH(TRUE,
(ISNUMBER(
FIND(
bird,
a
)
)))),
""))),
birdnamelength,
MAP(gridwords,
LAMBDA(a,
IFERROR(LEN(INDEX(bird,
XMATCH(TRUE,
(ISNUMBER(
FIND(
bird,
a
)
))))),
0))),
columnidentification,
MAP(
gridwords,
LAMBDA(
a,
SUM(
IFERROR(
FIND(
bird,
a
),
0
)
)
)
),
output,
MAKEARRAY(
10,
10,
LAMBDA(
r,
c,
IFS(
INDEX(
columnidentification,
r
)=0,
"x",
c=INDEX(
columnidentification,
r
)+INDEX(
birdnamelength,
r
),
"x",
TRUE(),
MID(
INDEX(
birdfound,
r
),
1+c-INDEX(
columnidentification,
r
),
1
)
)
)
),
output
) Excel solution 18 for Find Birds in Grid Rows, proposed by Ricardo Alexis Domínguez Hernández:
=LET(
s,
CONCAT(
LET(
c,
BYROW(
B2:K11,
LAMBDA(
x,
CONCAT(
x
)
)
),
LET(
r,
XLOOKUP(
"*"&M2:M8&"*",
c,
c,
,
2
),
BYROW(
XLOOKUP(
c,
r,
r,
REPT(
"x",
10
)
),
LAMBDA(
a,
IF(
a=REPT(
"x",
10
),
REPT(
"x",
10
),
XLOOKUP(
a,
r,
LET(
rows,
LET(
con,
BYROW(
B2:K11,
LAMBDA(
x,
CONCAT(
x
)
)
),
XLOOKUP(
"*"&M2:M8&"*",
con,
con,
,
2
)
),
birds,
XLOOKUP(
"*"&M2:M8&"*",
LET(
con,
BYROW(
B2:K11,
LAMBDA(
x,
CONCAT(
x
)
)
),
XLOOKUP(
"*"&M2:M8&"*",
con,
con,
,
2
)
),
M2:M8,
,
2
),
REPT(
"x",
SEARCH(
birds,
rows
)-1
)&birds&REPT(
"x",
10-SEARCH(
birds,
rows
)-LEN(
birds
)+1
)
)
)
)
)
)
)
)
),
WRAPROWS(
MID(
s,
SEQUENCE(
,
LEN(
s
)
),
1
),
10
)
)Excel solution 19 for Find Birds in Grid Rows, proposed by Josh Brodrick:
=LET(
a,
CONCAT(
A2:J11
),
w,
L2:L8,
s,
MAP(
w,
LAMBDA(
x,
IFERROR(
FIND(
x,
a
),
""
)
)
),
e,
IFNA(
LEN(
w
)+s,
""
),
l,
WRAPCOLS(
SORT(
VSTACK(
0,
s,
e
)
),
2
),
c,
IFERROR(
TOCOL(
CHOOSEROWS(
l,
2
)-CHOOSEROWS(
l,
1
)
),
""
),
col,
FILTER(
c,
c<>""
),
t,
HSTACK(
SORT(
FILTER(
HSTACK(
w,
s,
e
),
s<>""
),
2
),
col
),
u,
CONCAT(
REPT(
"x",
CHOOSECOLS(
t,
4
)
)&CHOOSECOLS(
t,
1
)
),
WRAPROWS(
DROP(
MID(
SUBSTITUTE(
u,
" ",
""
),
SEQUENCE(
1,
LEN(
u
)
),
1
),
,
1
),
10
)
)Excel solution 20 for Find Birds in Grid Rows, proposed by Tyler Cameron:
=DROP(REDUCE("",
BYROW(
B2:K11,
LAMBDA(
x,
LET(
b,
CONCAT(
x
),
REDUCE(
"",
M2:M8,
LAMBDA(
x,
y,
x&IFERROR(
FIND(
y,
b
)&","&y,
""
)
)
)
)
)
),
LAMBDA(x,
y,
LET(e,
IFNA(
--TEXTBEFORE(
y,
","
),
0
),
f,
TEXTAFTER(
y,
","
),
t,
EXPAND(
{"x"},
,
IF(
e=0,
10,
e-1
),
"x"
),
p,
TOROW(
MID(
f,
SEQUENCE(
LEN(
f
)
),
1
)
),
VSTACK(x,
IF(e=0,
t,
IF(AND(
e=1,
COUNTA(
p
)=10
),
p,
IF(e>1,
HSTACK(t,
p,
EXPAND({"x"},
,
10-COUNTA(
p
)-(e-1),
"x")),
HSTACK(p,
EXPAND({"x"},
,
10-COUNTA(
p
)-(e-1),
"x"))))))))),
1)Solving the challenge of Fi&nd Birds in Grid Rows with Python
Python solution 1 for Find Birds in Grid Rows, proposed by Konrad Gryczan, PhD:
import pandas as pd
import re
input = pd.read_excel("443 Birds Search.xlsx", header=None, skiprows=1, usecols="B:K")
list = pd.read_excel("443 Birds Search.xlsx", usecols="M:M", nrows = 7)
test = pd.read_excel("443 Birds Search.xlsx", header=None, skiprows=1, usecols="O:X")
test.columns = input.columns
input_united = input.apply(lambda x: ''.join(x.dropna().astype(str)), axis=1)
search = input_united.apply(lambda x: re.search('|'.join(list['Birds']), x))
span = search.apply(lambda x: x.span() if x else None)
output = []
for i in range(len(span)):
if span[i]:
start = span[i][0]
end = span[i][1]
row = i
for j in range(start, end):
output.append((row, j))
input2 = input.copy()
for i in range(len(input2)):
for j in range(len(input2.columns)):
if (i,j) in output:
pass
else:
input2.iloc[i,j] = 'x'
print(input2.equals(test)) # True
Solving the challenge of Find Birds in Grid Rows with Python in Excel
Python in Excel solution 1 for Find Birds in Grid Rows, proposed by Abdallah Ally:
import pandas as pd
file_path = 'Excel_Challenge_443 - Birds Search.xlsx'
df1 = pd.read_excel(file_path, usecols='B:K', skiprows=0)
df2 = pd.read_excel(file_path, usecols='M', nrows=7)
# Perform data transformation and cleansing
joined_chars = [''.join(x) for x in df1.values]
words1 = []
for x in joined_chars:
for y in df2.Birds:
x = x.replace(y, y.upper())
words1.append(x)
words2 = []
for x in words1:
for y in x:
words2.append(word)
df = pd.DataFrame([list(x) for x in words2])
df
Python in Excel solution 2 for Find Birds in Grid Rows, proposed by ferhat CK:
alan2=np.array(xl("B2:K11"))
kus=xl("M1:M8", headers=True)
arr2 = np.full(shape=(10, 10),fill_value="x")
for i in kus.Birds:
for n in range(10):
sira="".join(alan2[n]).find(i, 0)
if sira>=0:
m=0
for j in range(sira,sira+len(i)):
arr2[n,j]=i[m]
m+=1
arr2
Solving the challenge of Find Birds in Grid Rows with R
R solution 1 for Find Birds in Grid Rows, proposed by Anil Kumar Goyal:
Interesting Challenge
library(readxl)
library(tidyverse)
mat <- read_xlsx("Excel/Excel_Challenge_443 - Birds Search.xlsx",
range = "B2:K11",
col_names = FALSE) %>%
as.matrix()
birds <- read_xlsx("Excel/Excel_Challenge_443 - Birds Search.xlsx",
range = cell_cols("M"))
paste0(t(mat), collapse = "") %>%
str_replace_all(
birds %>%
mutate(rep_c = map_chr(Birds, ~str_c(rep("X", nchar(.x)), collapse = ""))) %>%
tibble::deframe()
) %>%
str_split("") %>%
unlist() %>%
matrix(nrow=nrow(mat), byrow = TRUE) %>%
{replace(mat, mat == ., "x")}
&