List the subjects from column A which appear in column B within +1 or -1 position to column A. Hence for a subject in row 12 for column A, the same subject should appear in either in row 11 or 12 or 10 of column B. Maths appears in row 4 of column A and row 5 of column B, hence a valid choice.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 324
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of List subjects appearing within +/-1 row in column B with Power Query
Power Query solution 1 for List subjects appearing within +/-1 row in column B, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
L = Source[Subjects2],
F = Number.From,
S = List.Accumulate(
List.Positions(L),
{},
(s, c) =>
s
& List.Intersect(
{
List.Transform(
{c - 1 + F(c = 0) .. c + 1 - F(c = List.Count(L) - 1)},
each Source[Subjects1]{_}
),
{L{c}}
}
)
)
in
SPower Query solution 2 for List subjects appearing within +/-1 row in column B, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Listas = Table.ToColumns(Source) &{List.Skip(Source[Subjects2])&{null}}&{{null}&List.RemoveLastN(Source[Subjects2])},
Sol = List.Transform(List.Select(List.Zip(Listas), each _{0} = _{1} or _{0} = _{2} or _{0} = _{3} ), each _{0})
in
Sol
o este....
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Listas = Table.ToColumns(Source) &{List.Skip(Source[Subjects2])&{null}}&{{null}&List.RemoveLastN(Source[Subjects2])},
Sol = List.Transform(List.Select(List.Zip(Listas), each List.Contains(List.Skip(_), _{0})), each _{0})
in
Sol
Power Query solution 3 for List subjects appearing within +/-1 row in column B, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Subs1 = List.Zip({Source[Subjects1], List.Positions(Source[Subjects1])}),
Subs2 = List.Zip({Source[Subjects2], List.Positions(Source[Subjects2])}),
Sel = List.Transform(
Subs1,
each List.RemoveNulls(
List.Transform(
Subs2,
(x) => if Text.Contains(_{0}, x{0}) then Number.Abs(_{1} - x{1}) else null
)
)
),
Sol = Table.SelectRows(
Table.ExpandListColumn(Table.FromRows(List.Zip({Source[Subjects1], Sel}), {"Answer", "B"}), "B"),
each [B] < 2
)[[Answer]]
in
SolPower Query solution 4 for List subjects appearing within +/-1 row in column B, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
res = [
a = List.Transform(Table.ToColumns(Fonte), (x) => Table.FromColumns({x} & {List.Positions(x)})),
b = Table.AddColumn(
a{0},
"join",
each List.Max(Table.SelectRows(a{1}, (x) => [Column1] = x[Column1])[Column2]? ?? null)
),
c = Table.SelectRows(
b,
each [Column2] = [join] or [Column2] - [join] = 1 or [Column2] - [join] = - 1
)
][c][Column1]
in
resPower Query solution 5 for List subjects appearing within +/-1 row in column B, proposed by Alexis Olson:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddShifts = Table.FromColumns(
Table.ToColumns(Source) & {List.Skip(Source[Subjects1])} & {{null} & Source[Subjects1]}
),
Filter = Table.SelectRows(
AddShifts,
each List.Contains({[Column1], [Column3], [Column4]}, [Column2]) and [Column2] <> null
)[Column2]
in
FilterPower Query solution 6 for List subjects appearing within +/-1 row in column B, proposed by Brian Julius:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddRelPos = Table.AddColumn(
Source,
"RelativePos",
each [
a = Source[Subjects1],
b = Source[Subjects2],
c = List.PositionOf(a, [Subjects1]),
d = List.PositionOf(b, [Subjects1], Occurrence.All),
e = List.Transform(d, each Number.Abs(_ - c)),
f = if List.Count(d) = 0 then 100 else List.Min(e),
g = if f > 1 then 0 else 1
][g]
),
Filter = Table.SelectColumns(Table.SelectRows(AddRelPos, each ([RelativePos] = 1)), "Subjects1")
in
FilterPower Query solution 7 for List subjects appearing within +/-1 row in column B, proposed by Ramiro Ayala Chávez:
let
Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content],
a = Table.InsertRows(Origen, 0, {[Subjects1 = null, Subjects2 = null]}),
b = a[Subjects1],
c = a[Subjects2],
d = List.Skip(c),
e = {null} & c,
f = Table.FromColumns({b, c, d, e}),
g = Table.AddColumn(
f,
"Answer Expected",
each
if [Column1] = [Column2] or [Column1] = [Column3] or [Column1] = [Column4] then
[Column1]
else
null
)[[Answer Expected]],
Sol = Table.SelectRows(g, each [Answer Expected] <> null)
in
SolPower Query solution 8 for List subjects appearing within +/-1 row in column B, proposed by Rafael González B.:
let
Source = Excel.CurrentWorkbook(){0}[Content],
T = Table.AddColumn(Source, "Check", each
let
a = [Subjects1],
b = Table.Column(Source, "Subjects1"),
c = Table.Column(Source, "Subjects2"),
d = List.PositionOf(b,a),
e = try List.Range(c,d-1,3) otherwise List.Range(c,d,2),
f = List.PositionOf(e,a),
g = f >= 0
in
g),
Result = Table.SelectRows(T, each ([Check] = true)) [[Subjects1]]
in
Result
🧙♂️ 🧙♂️ 🧙♂️
Power Query solution 9 for List subjects appearing within +/-1 row in column B, proposed by Nir Robinson:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
IndexCurrent = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
IndexMinus1 = Table.AddIndexColumn(IndexCurrent, "Index0", 0, 1, Int64.Type),
IndexPlus1 = Table.AddIndexColumn(IndexMinus1, "Index2", 2, 1, Int64.Type),
Merge1 = Table.NestedJoin(
IndexPlus1,
{"Index"},
IndexPlus1,
{"Index0"},
"Added index 2",
JoinKind.LeftOuter
),
Expanded1 = Table.ExpandTableColumn(Merge1, "Added index 2", {"Subjects2"}, {"Subjects2.1"}),
Merge2 = Table.NestedJoin(
Expanded1,
{"Index"},
Expanded1,
{"Index2"},
"Expanded Added index 2",
JoinKind.LeftOuter
),
Expande2 = Table.ExpandTableColumn(
Merge2,
"Expanded Added index 2",
{"Subjects2"},
{"Subjects2.2"}
),
Check = Table.AddColumn(
Expande2,
"check",
each
if [Subjects1] = [Subjects2] or [Subjects1] = [Subjects2.1] or [Subjects1] = [Subjects2.2] then
1
else
0
),
Filter = Table.SelectRows(Check, each ([check] = 1))
in
FilterPower Query solution 10 for List subjects appearing within +/-1 row in column B, proposed by Alejandra Horvath CPA, CGA:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
I = Table.AddIndexColumn(Source, "I", 0, 1, Int64.Type),
A = Table.AddColumn(
I,
"C",
each try
if [Subjects1] = [Subjects2] then
1
else if [Subjects2] = I[Subjects1]{[I] - 1} then
1
else if [Subjects2] = I[Subjects1]{[I] + 1} then
1
else
0
otherwise
0
),
Sol = Table.SelectRows(A, each ([C] = 1))[Subjects2]
in
SolSolving the challenge of List subjects appearing within +/-1 row in column B with Excel
Excel solution 1 for List subjects appearing within +/-1 row in column B, proposed by Bo Rydobon 🇹🇭:
=LET(
a,
A2:A20,
FILTER(
a,
COUNTIF(
OFFSET(
a,
SEQUENCE(
ROWS(
a
)
)-2,
1,
3
),
a
)
)
)Excel solution 2 for List subjects appearing within +/-1 row in column B, proposed by Rick Rothstein:
=FILTER(
A2:A20,
ISNUMBER(
FIND(
A2:A20,
B1:B19&" "&B2:B20&" "&B3:B21
)
)
)Excel solution 3 for List subjects appearing within +/-1 row in column B, proposed by Rick Rothstein:
=FILTER(
A2:A20,
MAP(
A2:A20,
LAMBDA(
x,
ISNUMBER(
MATCH(
x,
OFFSET(
x,
-1,
1,
3
),
0
)
)
)
)
)Excel solution 4 for List subjects appearing within +/-1 row in column B, proposed by John V.:
=LET(s,
A2:A20,
FILTER(s,
(s=B1:B19)+(s=B2:B20)+(s=B3:B21)))Excel solution 5 for List subjects appearing within +/-1 row in column B, proposed by محمد حلمي:
=
LET(a,
A2:A20,
FILTER(a,
(B2:B20=a)+(B1:B19=a)+(B3:B21=a)))Excel solution 6 for List subjects appearing within +/-1 row in column B, proposed by محمد حلمي:
=FILTER(
A2:A20,
MAP(
A2:A20,
LAMBDA(
a,
OR(
a=OFFSET(
a,
-1,
1,
3
)
)
)
)
)Excel solution 7 for List subjects appearing within +/-1 row in column B, proposed by محمد حلمي:
=LET(
b,
B2:B20,
FILTER(
b,
IFNA(
XMATCH(
XMATCH(
b,
A2:A20
)-SEQUENCE(
ROWS(
b
)
),
{-1,
0,
1}
),
)
)
)Excel solution 8 for List subjects appearing within +/-1 row in column B, proposed by Kris Jaganah:
=LET(a,
A2:A20,
b,
B2:B20,
c,
XMATCH(
a,
a
),
FILTER(a,
(a=b)+(a=XLOOKUP(
c-1,
c,
b,
""
))+(a=XLOOKUP(
c+1,
c,
b,
""
))))Excel solution 9 for List subjects appearing within +/-1 row in column B, proposed by Kris Jaganah:
=LET(a,A2:A20,b,B2:B20,c,XMATCH(a,a),FILTER(a,MMULT(--(XLOOKUP(c-{1,0,-1},c,b,0)=a),{1;1;1})))Excel solution 10 for List subjects appearing within +/-1 row in column B, proposed by Kris Jaganah:
=LET(a,A2:A20,b,B2:B20,c,XMATCH(a,a),FILTER(a,MMULT(--(XLOOKUP(c-{1,0,-1},c,b,0)=a),{1;1;1})))Excel solution 11 for List subjects appearing within +/-1 row in column B, proposed by Timothée BLIOT:
=LET(A,A2:A20,B,B2:B20, FILTER(A,MAP(SEQUENCE(ROWS(A)),LAMBDA(x, ISNUMBER(XMATCH(INDEX(A,x),INDEX(B,SEQUENCE(3,,x-1))))))))Excel solution 12 for List subjects appearing within +/-1 row in column B, proposed by Nikola Z Grujicic - Nikola Ž Grujičić:
=LET(a,
A2:A20,
b,
B2:B20,
c,
SEQUENCE(
ROWS(
a
)
),
d,
IF(
c+1=COUNTA(
c
)+1,
COUNTA(
c
),
c+1
),
e,
IF(
c-1=0,
1,
c-1
),
f,
FILTER(a,
(a=INDEX(
b,
c
))+(a=INDEX(
b,
d
))+(a=INDEX(
b,
e
))),
f)Excel solution 13 for List subjects appearing within +/-1 row in column B, proposed by Hussein SATOUR:
=LET(
a,
A2:A20,
b,
BYROW(
SEQUENCE(
COUNTA(
a
)
),
LAMBDA(
x,
ARRAYTOTEXT(
IFERROR(
INDEX(
B2:B20,
x+{-1,
0,
1}
),
0
)
)
)
),
FILTER(
a,
LEN(
b
)- LEN(
SUBSTITUTE(
b,
a,
""
)
)<>0
)
)Excel solution 14 for List subjects appearing within +/-1 row in column B, proposed by Duy Tùng:
=FILTER(A2:A20,
(A2:A20=B3:B21)+(A2:A20=B1:B19)+(A2:A20=B2:B20))Excel solution 15 for List subjects appearing within +/-1 row in column B, proposed by Sunny Baggu:
=FILTER(
A2:A20,
MAP(
A2:A20,
LAMBDA(
a,
OR(
IFERROR(
ABS(
XMATCH(
a,
A2:A20,
,
{1,
0,
-1}
) -
XMATCH(
a,
B2:B20,
,
{1,
0,
-1}
& )
) = {1; 0},
FALSE
)
)
)
)
)Excel solution 16 for List subjects appearing within +/-1 row in column B, proposed by Sunny Baggu:
=TOCOL(
IFS(
(A2:A20 = B2:B20) + (A2:A20 = B1:B19),
A2:A20,
A1:A19 = B2:B20,
B2:B20
),
3
)Excel solution 17 for List subjects appearing within +/-1 row in column B, proposed by LEONARD OCHEA 🇷🇴:
=TOCOL(
MAP(
A2:A20,
LAMBDA(
a,
IF(
OR(
a=INDEX(
B:B,
ROW(
a
)+{-1;0;1}
)
),
a,
1/0
)
)
),
3
)Excel solution 18 for List subjects appearing within +/-1 row in column B, proposed by LEONARD OCHEA 🇷🇴:
=TOCOL(
MAP(
A2:A20,
LAMBDA(
a,
IF(
OR(
a=OFFSET(
a,
-1,
1,
3
)
),
a,
x
)
)
),
2
)Excel solution 19 for List subjects appearing within +/-1 row in column B, proposed by Abdallah Ally:
=LET(a,A2:A20,f,LAMBDA(x,(x=OFFSET(x,0,1))+(x=OFFSET(x,1,1))+(x=OFFSET(x,-1,1))),TOCOL(IF(f(a),a,1/0),2))Excel solution 20 for List subjects appearing within +/-1 row in column B, proposed by Abdallah Ally:
=LET(a,
A2:A20,
f,
LAMBDA(x,
(x=OFFSET(
x,
0,
1
))+(x=OFFSET(
x,
1,
1
))+(x=OFFSET(
x,
-1,
1
))),
FILTER(
a,
MAP(
a,
LAMBDA(
x,
f(
x
)
)
)
))Excel solution 21 for List subjects appearing within +/-1 row in column B, proposed by Md. Zohurul Islam:
=LET(
u,
A2:A20,
v,
B2:B20,
sq,
SEQUENCE(
ROWS(
u
)
),
w,
MAP(
u,
LAMBDA(
x,
LET(
n,
FILTER(
sq,
u=x
),
m,
VSTACK(
n-1,
n,
n+1
),
a,
IFNA(
XMATCH(
x,
INDEX(
v,
m
)
),
0
),
a
)
)
),
z,
FILTER(
u,
w
),
z
)Excel solution 22 for List subjects appearing within +/-1 row in column B, proposed by Asheesh Pahwa:
=FILTER(
F5:F23,
DROP(
REDUCE(
"",
SEQUENCE(
ROWS(
G5:G23
)
) LAMBDA(
x,
y,
VSTACK(
x,
LET(
a,
OFFSET(
INDEX(
G4:G23,
y,
),
,
,
3
),
d,
IF(
a=0,
1,
a
),
b,
FIND(
d,
INDEX(
F5:F23,
y,
)
),
c,
OR(
ISNUMBER(
b
)
),
c
)
)
)
),
1
)
)Excel solution 23 for List subjects appearing within +/-1 row in column B, proposed by Julien Lacaze:
=FILTER(
A2:A20,
MAP(
A2:A20,
SEQUENCE(
ROWS(
A2:A20
)
),
LAMBDA(
a,
b,
OR(
INDEX(
B2:B21,
SEQUENCE(
3,
,
b-1
)
)=a
)
)
)
)Excel solution 24 for List subjects appearing within +/-1 row in column B, proposed by Mey Tithveasna:
=LET(a,A2:A20, FILTER(a, ISNUMBER(MATCH(a, OFFSET(a,-1,1,3),0))))Excel solution 25 for List subjects appearing within +/-1 row in column B, proposed by Pieter de Bruijn:
=LET(a,
A2:A20,
TOCOL(REPT(a,
1/(INDEX(
B2:B20,
SEQUENCE(
ROWS(
a
)
)+{-1,
0,
1}
)=a)),
2))Excel solution 26 for List subjects appearing within +/-1 row in column B, proposed by Nicolas Micot:
=LET(
_num;
SEQUENCE(
LIGNES(
A2:A20
)
);
_closestDistance;
MAP(
A2:A20;
_num;
LAMBDA(
l_s1;
l_num;
MIN(
ABS(
l_num-SI(
B2:B20=l_s1;
_num;
-10
)
)
)
)
);
FILTRE(
A2:A20;
_closestDistance<=1;
""
)
)Excel solution 27 for List subjects appearing within +/-1 row in column B, proposed by Ziad A.:
=FILTER(
A2:A20,
MMULT(
N(
A2:A20={B1:B19,
B2:B20,
B3:B21}
),
{1;1;1}
)
)Excel solution 28 for List subjects appearing within +/-1 row in column B, proposed by Giorgi Goderdzishvili:
=
LET(
sb,
A2:A20,
sbs,
B1:B20,
fin,
FILTER(sb,
(sb=DROP(
sbs,
-1
))+ (sb=DROP(
sbs,
1
))+(sb=VSTACK(
DROP(
sbs,
2
),
""
))),
fin)Excel solution 29 for List subjects appearing within +/-1 row in column B, proposed by Edwin Tisnado:
=LET(a,
A2:A20,
FILTRAR(a,
--(a=DESREF(
a,
,
1
))--(a=DESREF(
a,
-1,
1
))--(a=DESREF(
a,
1,
1
))))Excel solution 30 for List subjects appearing within +/-1 row in column B, proposed by Edwin Tisnado:
=LET(a,
A2:A20,
b,
B2:B20,
FILTER(a,
--(a=b)--(a=OFFSET(
b,
-1,
))--(a=OFFSET(
b,
1,
))))Excel solution 31 for List subjects appearing within +/-1 row in column B, proposed by Abdelrahman Omer, MBA, PMP:
=LET(a,
A2:A20,
b,
B2:B20,
FILTER(a,
MAP(a,
LAMBDA(x,
ABS(SUM((x=a)*SEQUENCE(
COUNTA(
a
)
))-SUM((x=b)*SEQUENCE(
COUNTA(
b
)
)))<2))))Excel solution 32 for List subjects appearing within +/-1 row in column B, proposed by Daniel Garzia:
=LET(a,
A2:A20,
TOCOL(IFS((a=B1:B19)+(a=B2:B20)+(a=B3:B21),
a),
2))Excel solution 33 for List subjects appearing within +/-1 row in column B, proposed by Hazem Hassan:
=LET(
a,
A2:A20,
b,
MATCH(
a,
a,
0
)+{-1,
0,
1},
CHOOSEROWS(
a,
TOCOL(
IF(
b=MATCH(
B2:B20,
a,
0
),
b,
1/0
),
3
)
)
)Excel solution 34 for List subjects appearing within +/-1 row in column B, proposed by Hazem Hassan:
=LET(
a,
A2:A20,
b,
MATCH(
a,
a,
0
)+{-1,
0,
1},
c,
INDEX(
B2:B20,
b
),
TOCOL(
IF(
c=a,
c,
1/0
),
3
)
)Excel solution 35 for List subjects appearing within +/-1 row in column B, proposed by Gabriel Raigosa:
=LET(x,
A2:A21,
FILTER(x,
(x=OFFSET(
x,
-1,
1
))+(x=OFFSET(
x,
0,
1
))+(x=OFFSET(
x,
1,
1
))))Excel solution 36 for List subjects appearing within +/-1 row in column B, proposed by Luis Couto:
=FILTRAR(
A2:A20;
MAP(
A2:A20;
LAMBDA(
d;
O(
DESREF(
d;
-1;
1;
3;
1
)=d
)
)
)
)Excel solution 37 for List subjects appearing within +/-1 row in column B, proposed by Nir Robinson:
=IF(
OR(
A4=B4,
A4=B3,
A4=B5
),
1,
0
)Excel solution 38 for List subjects appearing within +/-1 row in column B, proposed by Makesh M:
=filter(A:A20, isnumber(match(A:A20,B:B20,0)))Solving the challenge of List subjects appearing within +/-1 row in column B with Python in Excel
Python in Excel solution 1 for List subjects appearing within +/-1 row in column B, proposed by John V.:
Hi everyone!
[i for i, a, b, c in zip(xl("A2:A20")[0], n, n[1:], n[2:]) if i in [a, b, c]]
Blessings!
Solving the challenge of List subjects appearing within +/-1 row in column B with R
R solution 1 for List subjects appearing within +/-1 row in column B, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Plus Minus 1 Row.xlsx", range = "A1:B20")
test = read_excel("Plus Minus 1 Row.xlsx", range = "C1:C6")
r1 = input %>%
select(s = 1) %>%
mutate(n = row_number())
r2 = input %>%
select(s = 2) %>%
mutate(n = row_number())
result = r1 %>%
left_join(r2, by =c("s")) %>%
mutate(diff = abs(n.x - n.y)) %>%
filter(diff <= 1) %>%
select(s)
Solving the challenge of List subjects appearing within +/-1 row in column B with DAX
DAX solution 1 for List subjects appearing within +/-1 row in column B, proposed by Zoran Milokanović:
EVALUATE
SELECTCOLUMNS(FILTER(ADDCOLUMNS(Input,
"Last", CALCULATE(MAX(Input[Subjects1]), OFFSET(-1, ORDERBY(Input[Index])), REMOVEFILTERS(Input)),
"Next", CALCULATE(MAX(Input[Subjects1]), OFFSET(1, ORDERBY(Input[Index])), REMOVEFILTERS(Input))
), Input[Subjects1] = Input[Subjects2]||[Last] = Input[Subjects2]||[Next] = Input[Subjects2]
), Input[Subjects2])
&&