Words Around SELECT

Provide a formula to list all bird names which appear immediately before and after SELECT in range A2:A20. Parrot appears immediately before SELECT and Dove appear immediately after SELECT. Hence, they will appear as one of the answers.

📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 16
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Words Around SELECT with Power Query

Power Query solution 1 for Words Around SELECT, proposed by Aditya Kumar Darak 🇮🇳:

let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Position = List.PositionOf(Source[Birds], "Select", Occurrence.All, Comparer.OrdinalIgnoreCase), 
  All = List.Sort(List.Transform(Position, (f) => f - 1) & List.Transform(Position, (f) => f + 1)), 
  Values = List.RemoveNulls(List.Transform(All, (f) => try Source[Birds]{f} otherwise null)), 
  Result = Table.FromList(Values, null, type table [Birds = text])
in
  Result

Power Query solution 2 for Words Around SELECT, proposed by Aditya Kumar Darak 🇮🇳:

let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Index = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type), 
  Calc = Table.AddColumn(
    Index, 
    "Calc", 
    each try
      Source[Birds]{[Index] - 1} = "SELECT" or Source[Birds]{[Index] + 1} = "SELECT"
    otherwise
      null
  ), 
  Result = Table.SelectRows(Calc, each ([Calc] = true))[[Birds]]
in
  Result

Power Query solution 3 for Words Around SELECT, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Index = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type), 
  List = List.Combine(
    Table.AddColumn(
      Index, 
      "New", 
      each 
        if [Birds] = "SELECT" then
          {Source[Birds]{[Index] - 1}} & {Source[Birds]{[Index] + 1}}
        else
          {}
    )[New]
  ), 
  #"Errors?" = Table.FromColumns({List}, {"Answer Expected"}), 
  Solucion = Table.RemoveRowsWithErrors(#"Errors?", {"Answer Expected"})
in
  Solucion

Power Query solution 4 for Words Around SELECT, proposed by Luan Rodrigues:

let
  Fonte = Excel.CurrentWorkbook(){[Name = "Tabela1"]}[Content], 
  Indice = Table.AddIndexColumn(Fonte, "Índice", 1, 1, Int64.Type), 
  Birds1 = Table.AddColumn(Indice, "Birds1", each [Índice] - 1), 
  Birds2 = Table.AddColumn(Birds1, "Birds2", each [Índice] + 1), 
  Tabela = Table.SelectRows(Birds2, each [Birds] = "SELECT"), 
  Tab1 = Table.NestedJoin(Tabela, {"Birds1"}, Indice, {"Índice"}, "Birds_1", JoinKind.LeftOuter), 
  Tab2 = Table.NestedJoin(Tab1, {"Birds2"}, Indice, {"Índice"}, "Birds_2", JoinKind.LeftOuter)[
    [Birds_1], 
    [Birds_2]
  ], 
  Expand = Table.SelectRows(
    Table.Combine(
      {
        Table.ExpandTableColumn(Tab2, "Birds_2", {"Birds", "Índice"}, {"Birds", "Índice"}), 
        Table.ExpandTableColumn(Tab2, "Birds_1", {"Birds", "Índice"}, {"Birds", "Índice"})
      }
    ), 
    each [Birds] <> null
  ), 
  Order = Table.Sort(Expand, {{"Índice", Order.Ascending}})[[Birds]]
in
  Order

Power Query solution 5 for Words Around SELECT, proposed by Brian Julius:

Melissa - Looks pretty efficient to me. That ListAnyTrue/List.Transform combo is 🔥🔥🔥

Power Query solution 6 for Words Around SELECT, proposed by Brian Julius:

let
  Source = #"BirdNames Raw", 
  Index = Table.AddIndexColumn(Source, "Positions", 0, 1), 
  CountSelects = List.Count(List.Select(Table.ToList(Source), each _ = "SELECT")), 
  SelectPositions = List.PositionOf(Table.ToList(Source), "SELECT", CountSelects), 
  BirdPositions = List.Combine(
    {List.Transform(SelectPositions, each _ - 1), List.Transform(SelectPositions, each _ + 1)}
  ), 
  BirdTable = (
    Table.SelectColumns(
      Table.SelectRows(
        Table.AddColumn(
          Index, 
          "AnswerExpected", 
          each if List.Contains(BirdPositions, _[Positions]) then _[Birds] else null
        ), 
        each [AnswerExpected] <> null
      ), 
      {"AnswerExpected"}
    )
  )
in
  BirdTable

Power Query solution 7 for Words Around SELECT, proposed by Antriksh Sharma:

let
  Source = BirdsDataSource, 
  AddedIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type), 
  AddedCustom = Table.AddColumn(
    AddedIndex, 
    "Custom", 
    (Outer) =>
      let
        BirdsTable = AddedIndex, 
        MyIndex = Table.SelectRows(
          BirdsTable, 
          (Inner) => Inner[Birds] = "SELECT" and Outer[Index] = Inner[Index]
        )[Index]{0}?, 
        Positions = {MyIndex - 1, MyIndex + 1}, 
        Result = List.Transform(Positions, each try BirdsTable[Birds]{_} otherwise null)
      in
        Result
  ), 
  Result = List.RemoveNulls(List.Combine(AddedCustom[Custom]))
in
  Result

Power Query solution 8 for Words Around SELECT, proposed by Venkata Rajesh:

let
  Source = BeforeandAfter, 
  OutPut = Table.SelectColumns(
    Table.SelectRows(
      Table.AddColumn(
        Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type), 
        "Custom", 
        each try
          
            if Source{[Index] - 1}[Birds] = "SELECT" or Source{[Index] + 1}[Birds] = "SELECT" then
              1
            else
              0
        otherwise
          null
      ), 
      each ([Custom] = 1)
    ), 
    "Birds"
  )
in
  OutPut

Power Query solution 9 for Words Around SELECT, proposed by Melissa de Korte:

let others surprise me... This works. 

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 x = List.Buffer( List.PositionOf( y, "SELECT", Occurrence.All )),
 y = List.Buffer( Source[Birds] ),
 Result = List.Select( y, (z) => List.AnyTrue( List.Transform( x, each y{_-1}? = z or y{_+1}? = z ) ))
in
 Result

Power Query solution 10 for Words Around SELECT, proposed by Dominic Walsh:

let
  Source = Excel.CurrentWorkbook(){[Name = "BIRDS"]}[Content], 
  Index1 = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type), 
  Index0 = Table.AddIndexColumn(Index1, "Index.1", 0, 1, Int64.Type), 
  #"Index-1" = Table.AddIndexColumn(Index0, "Index.2", - 1, 1, Int64.Type), 
  Merged1 = Table.NestedJoin(
    #"Index-1", 
    {"Index.1"}, 
    #"Index-1", 
    {"Index"}, 
    "Renamed Columns", 
    JoinKind.LeftOuter
  ), 
  Expanded1 = Table.ExpandTableColumn(Merged1, "Renamed Columns", {"Column1"}, {"Column1.1"}), 
  Merged2 = Table.NestedJoin(
    Expanded1, 
    {"Index.1"}, 
    Expanded1, 
    {"Index.2"}, 
    "Expanded Renamed Columns", 
    JoinKind.LeftOuter
  ), 
  Expanded2 = Table.ExpandTableColumn(
    Merged2, 
    "Expanded Renamed Columns", 
    {"Column1"}, 
    {"Column1.2"}
  ), 
  Select = Table.SelectRows(Expanded2, each [Column1] = "SELECT"), 
  Remove = Table.SelectColumns(Select, {"Column1.1", "Column1.2"}), 
  Unpivot = Table.UnpivotOtherColumns(Remove, {}, "Attribute", "Value"), 
  Remove2 = Table.RemoveColumns(Unpivot, {"Attribute"})
in
  Remove2

Solving the challenge of Words Around SELECT with Excel

Excel solution 1 for Words Around SELECT, proposed by Rick Rothstein:

=let(a,a2:a20,s,"select",b,if(offset(a,-1,)=s,a,if(offset(a,1,)=s,a,"")),filter(b,b<>""))

Excel solution 2 for Words Around SELECT, proposed by Rick Rothstein:

=let(x,a2:a19,y,a3:a20,s,"select",b,if(y=s,x,if(x=s,y,0)),filter(b,b>0))

Excel solution 3 for Words Around SELECT, proposed by محمد حلمي:

=LET(A;A2:A20;TOCOL(INDEX(A;IF(A="SELECT";ROW(A)+{-2,0};1/0));3))

Excel solution 4 for Words Around SELECT, proposed by 🇰🇷 Taeyong Shin:

=TOCOL(REGEXEXTRACT(ARRAYTOTEXT(A2:A20),"w+(?=(?:, )SELECT)|(?<=SELECT(?:, ))w+",1))

Excel solution 5 for Words Around SELECT, proposed by Julian Poeltl:

=LET(B,A2:A20,P,FILTER(SEQUENCE(ROWS(B)),B="SELECT"),TOCOL(HSTACK(INDEX(B,P-1),INDEX(B,P+1)),3))

Excel solution 6 for Words Around SELECT, proposed by Aditya Kumar Darak 🇮🇳:

= LET(
 _calc,
 TOCOL(  (A2:A20 = "Select")
 * (SEQUENCE(ROWS(A2:A20)) + {1,-1})),
 CHOOSEROWS(
 A2:A20,
 FILTER(_calc, _calc > 0)))

Excel solution 7 for Words Around SELECT, proposed by Aditya Kumar Darak 🇮🇳:

= CHOOSEROWS(
 Birds,
 TOCOL(
 IF(Birds = "Select",
 SEQUENCE(ROWS(Birds)) + {1,-1},
 NA()),
 2))

Excel solution 8 for Words Around SELECT, proposed by Timothée BLIOT:

=TRANSPOSE(TEXTSPLIT(TEXTJOIN(".",TRUE,IF(A2:A20="Select",OFFSET(A2:A20,-1,0)&"."&OFFSET(A2:A20,1,0),"")),".",,TRUE))

Excel solution 9 for Words Around SELECT, proposed by Hussein SATOUR:

=LET(a, A2:A20, c, FILTER(SEQUENCE(COUNTA(a)), a = "SELECT"),
TOCOL(INDEX(a, c + {-1,1}), 2))

Excel solution 10 for Words Around SELECT, proposed by Oscar Mendez Roca Farell:

=IFERROR(OFFSET(A$1;AGGREGATE(15;6;AGGREGATE(15;6;ROW(A$1:A$20)/SEARCH("SELECT";A$1:A$20);ROW($A$1:INDEX($A:$A;COUNTIF(A$1:A$20;"SELECT"))))-{2};ROW(A1)););"")

Excel solution 11 for Words Around SELECT, proposed by Abdallah Ally:

=TOCOL(INDEX(A2:A20,FILTER(IF(--(A2:A20="SELECT")=1,ROW(A2:A20),""),ISNUMBER(IF(--(A2:A20="SELECT")=1,ROW(A2:A20),"")))+{-2,0}),3)

Excel solution 12 for Words Around SELECT, proposed by Bhavya Gupta:

=LET(a, FILTER(SEQUENCE(ROWS(Rng)), Rng="Select"), INDEX(Rng, VSTACK(a-1, a+1)))

Excel solution 13 for Words Around SELECT, proposed by Charles Roldan:

=LET(Birds, A2:A20, TOCOL(FILTER(INDEX(Birds, {-1,1} + SEQUENCE(ROWS(Birds))), Birds = "SELECT"), 3))

Excel solution 14 for Words Around SELECT, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:

=TEXTJOIN(;;IFERROR(INDEX(A2:A20;SMALL(IFERROR(IF(FIND("SELECT";$A$2:$A$20;1)=1;ROW(A2:A20);"");"");{1;2;3}));"") & "; "&INDEX(A2:A20;SMALL(IFERROR(IF(FIND("SELECT";$A$2:$A$20;1)=1;ROW(A2:A20)-2;"");"");{1;2;3})))

Excel solution 15 for Words Around SELECT, proposed by Jardiel Euflázio:

=TEXTSPLIT(TEXTJOIN(",",,IF(A2:A19="SELECT",A3:A20,IF(A3:A20="SELECT",A2:A19,""))),,",")

Excel solution 16 for Words Around SELECT, proposed by Jardiel Euflázio:

=LET(

a,A2:A20,
b,ROWS(a),
c,SEQUENCE(b),
d,INDEX(a,({-11}+c)/(a="SELECT")),
e,TOCOL(d),

FILTER(
e,ISTEXT(e)
)

)

Excel solution 17 for Words Around SELECT, proposed by Jardiel Euflázio:

=LET(
a,A2:A20,
b,ROWS(a),
c,SEQUENCE(b)*(a="SELECT"),
d,TOCOL(FILTER(c,c<>0)+{-11}),
e,INDEX(a,d),
FILTER(
e,ISTEXT(e)
)
)

Excel solution 18 for Words Around SELECT, proposed by Jardiel Euflázio:

=LET(
a,A2:A19,
b,A3:A20,
c,IF(a="SELECT",b,IF(b="SELECT",a,"")),
FILTER(
c,c<>""
)
)

Excel solution 19 for Words Around SELECT, proposed by Victor Momoh (MVP, MOS, R.Eng):

=FILTER($A$2:$A$20,(A3:A21="Select")+(A1:A19="Select"))

Excel solution 20 for Words Around SELECT, proposed by Abdelrahman Omer, MBA, PMP:

=LET(a,A2:A20,b,--(a="SELECT"),d,DROP(b,1),G,IFERROR(MID(CONCAT(0,b),SEQUENCE(COUNTA(a)),1),0),FILTER(a,IFERROR((d+G),0)))

Excel solution 21 for Words Around SELECT, proposed by Cary Ballard, DML:

=LET(
 a, A2:A20,
 b, IFS(a = "select", SEQUENCE(ROWS(a))),
 c, TOCOL(b, 2),
 TOCOL(INDEX(a, SORT(VSTACK(c - 1, c + 1))), 2)
)

Excel solution 22 for Words Around SELECT, proposed by RIJESH T.:

=LET(b,A2:A20,s,"select",TOCOL(IFS(OFFSET(b,-1,)=s,b,OFFSET(b,1,)=s,b),2))

Excel solution 23 for Words Around SELECT, proposed by Ibrahim Sadiq:

=TOCOL(LET(a,A1:A20,b,FILTER(ROW(a),a="SELECT"),c,VSTACK(b+1,b-1),INDEX(a,c,0)),3)

Solving the challenge of Words Around SELECT with Python in Excel

Python in Excel solution 1 for Words Around SELECT, proposed by Aditya Kumar Darak 🇮🇳:

data = xl("A1:A20", True)
before = data["Birds"].shift(1)
after = data["Birds"].shift(-1)
result = (
 pd.concat([before[data["Birds"] == "SELECT"], after[data["Birds"] == "SELECT"]])
 .reset_index(drop=True)
 .dropna()
)
result

Solving the challenge of Words Around SELECT with Power Query

Solving the challenge of Words Around SELECT with Excel

Solving the challenge of Words Around SELECT with Python in Excel

Leave a Reply Cancel reply