Home » Extract Last Odd Numbers

Extract Last Odd Numbers

Extract the last n odd numbers. Hence, if Numbers = 23, 7, 6, 5, 24 and n=2, then last 2 odd numbers are 7, 5. If n=0, then nothing would be extracted. If n > number of odd numbers in Numbers column, then all odd numbers will be extracted.

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

Solving the challenge of Extract Last Odd Numbers with Power Query

Power Query solution 1 for Extract Last Odd Numbers, proposed by Bo Rydobon 🇹🇭: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Ans = Table.TransformRows(
    Source, 
    each Text.Combine(
      List.LastN(List.Select(Text.Split([Numbers], ", "), (s) => Number.IsOdd(Number.From(s))), [n]), 
      ", "
    )
  )
in
  Ans
Power Query solution 2 for Extract Last Odd Numbers, proposed by Zoran Milokanović: 
let
  S = List.Transform(
    Table.ToRows(Excel.CurrentWorkbook(){[Name = "Input"]}[Content]), 
    each Text.Combine(
      List.LastN(
        List.Accumulate(
          Text.Split(_{0}, ", "), 
          {}, 
          (s, d) => if Number.IsOdd(Number.From(d)) then s & {d} else s
        ), 
        _{1}
      ), 
      ", "
    )
  )
in
  S
Power Query solution 3 for Extract Last Odd Numbers, proposed by 🇰🇷 Taeyong Shin: 
let
  Source = Excel.CurrentWorkbook(){[Name = "tblNumbers"]}[Content], 
  Transform = Table.TransformRows(
    Source, 
    each 
      let
        ToList  = Splitter.SplitTextByDelimiter(", ")([Numbers]), 
        Odd     = List.Select(ToList, each Number.IsOdd(Number.From(_))), 
        Combine = Combiner.CombineTextByDelimiter(", ")(List.LastN(Odd, [n]))
      in
        Combine
  ), 
  Tbl = Table.FromList(Transform, Splitter.SplitByNothing(), type table [Answer = text])
in
  Tbl
Power Query solution 4 for Extract Last Odd Numbers, proposed by Aditya Kumar Darak 🇮🇳: 
let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Return = Table.AddColumn(
    Source, 
    "Answer", 
    each [
      Split   = Text.Split([Numbers], ", "), 
      Odd     = List.Select(Split, (f) => Number.IsOdd(Number.From(f))), 
      Last    = List.LastN(Odd, [n]), 
      Combine = Text.Combine(Last, ", ")
    ][Combine]
  )
in
  Return
Power Query solution 5 for Extract Last Odd Numbers, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Sol = Table.AddColumn(
    Source, 
    "Answer", 
    each Text.Combine(
      List.Transform(
        List.LastN(
          List.Select(List.Transform(Text.Split([Numbers], ","), Number.From), Number.IsOdd), 
          [n]
        ), 
        Text.From
      ), 
      ","
    )
  )[[Answer]]
in
  Sol
Power Query solution 6 for Extract Last Odd Numbers, proposed by Luan Rodrigues: 
let
  Fonte = Tabela1, 
  res = Table.AddColumn(
    Fonte, 
    "Personalizar", 
    each Text.Combine(
      List.Transform(
        List.LastN(
          List.Combine(
            List.Transform(
              Text.Split([Numbers], ","), 
              each List.Select({Number.From(_)}, each Number.IsOdd(_))
            )
          ), 
          [n]
        ), 
        each Text.From(_)
      ), 
      ","
    )
  )
in
  res
Power Query solution 7 for Extract Last Odd Numbers, proposed by Brian Julius: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  AddReverseOdd = Table.AddColumn(
    Source, 
    "FilterAndReverse", 
    each [
      a  = [Numbers], 
      b  = Text.Split([Numbers], ", "), 
      b2 = List.Transform(b, each Number.From(_)), 
      c  = List.Select(b2, each Number.IsOdd(_)), 
      d  = List.Reverse(c)
    ][d]
  ), 
  AddFIrstN = Table.AddColumn(AddReverseOdd, "Answer", each List.FirstN([FilterAndReverse], [n])), 
  ExtractAndClean = Table.RemoveColumns(
    Table.TransformColumns(
      AddFIrstN, 
      {"Answer", each Text.Combine(List.Transform(_, Text.From), ", "), type text}
    ), 
    "FilterAndReverse"
  )
in
  ExtractAndClean
Power Query solution 8 for Extract Last Odd Numbers, proposed by Guillermo Arroyo: 
let
  Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content], 
  a      = List.Transform(Origen[Numbers], each Text.Split(_, ", ")), 
  b      = List.Transform(a, each List.Transform(_, each Number.FromText(_))), 
  c      = List.Transform(b, each List.Select(_, Number.IsOdd)), 
  d      = List.Zip({c, Origen[n]}), 
  e      = List.Transform(d, each List.LastN(_{0}, _{1})), 
  f      = List.Transform(e, each List.Transform(_, each Text.From(_))), 
  g      = List.Transform(f, each Text.Combine(_, ", "))
in
  g

Solving the challenge of Extract Last Odd Numbers with Excel

Excel solution 1 for Extract Last Odd Numbers, proposed by Bo Rydobon 🇹🇭: 
=MAP(A2:A7,B2:B7,LAMBDA(a,n,LET(s,TEXTSPLIT(a,","),ARRAYTOTEXT(IFERROR(TAKE(TOCOL(s/MOD(s,2),3),-n),"")))))
Excel solution 2 for Extract Last Odd Numbers, proposed by Rick Rothstein: 
=IFERROR(MAP(A2:A7,B2:B7,LAMBDA(a,b,LET(n,TEXTSPLIT(a,", "),TEXTJOIN(", ",,TAKE(FILTER(n,ISODD(n)),,-b))))),"")
Excel solution 3 for Extract Last Odd Numbers, proposed by John V.: 
=MAP(A2:A7,B2:B7,LAMBDA(a,b,LET(n,TEXTSPLIT(a,","),IFERROR(ARRAYTOTEXT(TAKE(TOCOL(n/ISODD(n),2),-b)),""))))
Excel solution 4 for Extract Last Odd Numbers, proposed by محمد حلمي: 
=MAP(A2:A7,B2:B7,LAMBDA(a,b,LET(
v,TEXTSPLIT(a,", "),
IFERROR(ARRAYTOTEXT(TAKE(FILTER(v,ISODD(v)),,-b)),""))))
Excel solution 5 for Extract Last Odd Numbers, proposed by Kris Jaganah: 
=MAP(A2:A7,B2:B7,LAMBDA(x,y,LET(a,TEXTSPLIT(x,,", "),b,FILTER(a,MOD(a,2)),c,SEQUENCE(ROWS(b),,ROWS(b),-1),IFERROR(ARRAYTOTEXT(FILTER(b,c<=y)),""))))
Excel solution 6 for Extract Last Odd Numbers, proposed by Julian Poeltl: 
=MAP(A2:A7,B2:B7,LAMBDA(N,NN,LET(SP,--TEXTSPLIT(N,", "),IFERROR(TEXTJOIN(", ",,TAKE(FILTER(SP,ISODD(SP)),,-NN)),""))))
Excel solution 7 for Extract Last Odd Numbers, proposed by Aditya Kumar Darak 🇮🇳: 
=MAP(
 A2:A7,
 B2:B7,
 LAMBDA(a, b,
 LET(
 s, TEXTSPLIT(a, , ", "),
 f, FILTER(s, ISODD(s), ""),
 r, ARRAYTOTEXT(IFERROR(TAKE(f, -b), "")),
 r
 )
 )
)
Excel solution 8 for Extract Last Odd Numbers, proposed by Timothée BLIOT: 
=LET(A, A2:A7, B, B2:B7, MAP(A,B, LAMBDA(a,b, TEXTJOIN(",",, IFERROR(TAKE(LET(Z, MAP(TEXTSPLIT(a,","), LAMBDA(x, IF(ISODD(x),x,"") )), FILTER(Z,Z<>"","") ),,-b),"") ) )) )
Excel solution 9 for Extract Last Odd Numbers, proposed by Hussein SATOUR: 
=MAP(A2:A7,B2:B7, LAMBDA(x,y, IFERROR(TEXTJOIN(", ",,TAKE(LET(a, TEXTSPLIT(x, ", "), FILTER(a, ISODD(a))),,-y)),"")))
Excel solution 10 for Extract Last Odd Numbers, proposed by Oscar Mendez Roca Farell: 
=DROP(REDUCE("", A2:A7&", "&B2:B7, LAMBDA(i, x, VSTACK(i, LET(_t, TEXTSPLIT(x, ","), _c, DROP(_t, ,-1), IFERROR(TEXTJOIN(", ", , TAKE( TOROW( IF( ISODD(_c), _c, NA()), 3), ,-1*RIGHT(x))), ""))))), 1)
Excel solution 11 for Extract Last Odd Numbers, proposed by Sunny Baggu: 
=MAP(A2:A7,B2:B7,LAMBDA(x,y,LET(_m,TEXTSPLIT(x,,", ")+0,IFERROR(ARRAYTOTEXT(TAKE(FILTER(_m,MAP(_m,LAMBDA(a,ISODD(a))),""),-y)),""))))
Excel solution 12 for Extract Last Odd Numbers, proposed by Md. Zohurul Islam: 
=MAP(A2:A7,B2:B7,LAMBDA(x,y,LET(
a,--TEXTSPLIT(x,", "),
b,IFERROR(FILTER(a,MOD(a,2)<>0),""),
d,IFERROR(ARRAYTOTEXT(TAKE(b,,-y)),""),
d)))
Excel solution 13 for Extract Last Odd Numbers, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert: 
=MAP(A2:A7;B2:B7;LAMBDA(e;w;TEXTJOIN(", ";;TAKE(LET(t;UNIQUE(ODD(SEQUENCE(100)));q;IFERROR(INDEX(t;MAP(VALUE(TOCOL(TEXTSPLIT(e;",")));LAMBDA(x;MATCH(x;t;0))));"");FILTER(q;q<>""));-w))))
Excel solution 14 for Extract Last Odd Numbers, proposed by Abhishek Kumar Jain: 
=IFERROR(MAP(A2:A7,B2:B7,LAMBDA(x,y,TEXTJOIN(",",1,LET(a,--TEXTSPLIT(x,", "), c,FILTER(a,ISODD(a)),b,IF(y>COUNT(c),COUNT(c),y),INDEX(c,,SEQUENCE(,b,COUNT(c)-b+1)))))),"")
Excel solution 15 for Extract Last Odd Numbers, proposed by Guillermo Arroyo: 
=MAP(A2:A7,B2:B7,LAMBDA(a,b,TEXTJOIN(", ",1,IF(b,TAKE(LET(n,--TEXTSPLIT(a,,", "),FILTER(n,ISODD(n),"")),-b),""))))
Excel solution 16 for Extract Last Odd Numbers, proposed by Anup Kumar: 
=MAP(A2:A7,B2:B7,LAMBDA(a,b,LET(
nbrs,--TEXTSPLIT(a,{","," "},,TRUE),
ansNbrs,TAKE(FILTER(nbrs,ISODD(nbrs)),,-b),
IFERROR(TEXTJOIN(", ",,ansNbrs),"")
)))
Excel solution 17 for Extract Last Odd Numbers, proposed by Gabriel Raigosa: 
=IFERROR(MAP(A2:A7,B2:B7,LAMBDA(x,y,LET(d,TEXTSPLIT(x,", "),r,MOD(d,2),TEXTJOIN(",",,TAKE(FILTER(IF(r,d,""),r),,-y))))),"") 

▶️ES:
 =SI.ERROR(MAP(A2:A7,B2:B7,LAMBDA(x,y,LET(d,DIVIDIRTEXTO(x,", "),r,RESIDUO(d,2),UNIRCADENAS(",",,TOMAR(FILTRAR(SI(r,d,""),r),,-y))))),"")
Excel solution 18 for Extract Last Odd Numbers, proposed by Miguel Angel Franco García: 
=LET(a;SI(ES. IMPAR(ABS(DIVIDIRTEXTO(A2;","))); ABS(DIVIDIRTEXTO(A2;","));""); SI(B2=0;""; SI(B2>CONTAR(a); UNIRCADENAS(","; VERDADERO;a); UNIRCADENAS(","; VERDADERO;ORDENAR(K.ESIMO. MAYOR(a; SECUENCIA(B2)))))))
Excel solution 19 for Extract Last Odd Numbers, proposed by Ben Gutscher: 
=IFERROR(LET(nums,TEXTSPLIT(A2,,", "),TEXTJOIN(", ",1,TAKE(FILTER(nums,MOD(nums,2)=1),-B2,1))),"")

Solving the challenge of Extract Last Odd Numbers with Excel VBA

Excel VBA solution 1 for Extract Last Odd Numbers, proposed by Vasin Nilyok: 
Sub LastOddNums()
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Dim NumArr() As String, NumCollection As New Collection
For r = 2 To LastRow
 iText = Cells(r, 1)
 nOdd = Cells(r, 2)
 If nOdd = 0 Then
 GoTo Skip
 Else
 NumArr = Split(iText, ",")
 For i = 1 To UBound(NumArr) + 1
 If NumArr(i - 1) Mod 2 <> 0 Then
 NumCollection.Add NumArr(i - 1)
 End If
 Next i
 nColl = NumCollection.Count
 If nColl = 0 Then
 GoTo Skip
 Else
 For n = WorksheetFunction.Max(nColl - nOdd + 1, 1) To nColl
 If n = WorksheetFunction.Max(nColl - nOdd + 1, 1) Then
 Ans = CStr(NumCollection(n))
 Else
 Ans = Ans & ", " & CStr(NumCollection(n))
 End If
 Next n
 End If
 Cells(r, 4) = Ans
 End If
Set NumCollection = New Collection
Ans = Empty
Erase NumArr()
Skip:
Next r
End Sub

&&&

Leave a Reply