Custom Sequence from Four Terms - Excel, Power Query, Python, Data Challenges

Generate a sequence taking first 4 numbers as 1, 2, 3, 4. In this sequence, take the block of last 4 numbers and next 2 numbers would sum of first and third term and sum of second and fourth terms. Derive 100 terms of this series. Starting terms: 1, 2, 3, 4 Next 2 terms = 1+3=4 and 2+4=6 Now block of last 4 terms would be 3, 4, 4, 6 Hence next 2 terms = 3+4=7, 4+6=10 Now block of last 4 terms = 4, 6, 7, 10 Hence next 2 numbers would be 4+7=11, 6+10=16

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

Solving the challenge of Custom Sequence from Four Terms with Power Query

Power Query solution 1 for Custom Sequence from Four Terms, proposed by John V.:

Hi everyone!
One {M} Option could be:
= Table.FromColumns({List.Accumulate({0..95}, {1..4}, (s, c) => s & {s{c} + s{c + 2}})}, {"Answer Expected"})
Blessings!

Power Query solution 2 for Custom Sequence from Four Terms, proposed by Kris Jaganah:

let
  A = {1, 2, 3, 4}
in
  Table.FromColumns(
    {
      List.Combine(
        {A}
          & List.Generate(
            () => [
              a = 0, 
              b = A, 
              c = {List.Sum(List.Alternate(b, 1, 1, 1))} & {List.Sum(List.Alternate(b, 1, 1))}
            ], 
            each [a] < 48, 
            each [
              a = [a] + 1, 
              b = List.LastN([b], 2) & [c], 
              c = {List.Sum(List.Alternate(b, 1, 1, 1))} & {List.Sum(List.Alternate(b, 1, 1))}
            ], 
            each [c]
          )
      )
    }, 
    {"Answer Expected"}
  )

Power Query solution 3 for Custom Sequence from Four Terms, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Sol = List.Last(
    List.Generate(
      () => [x = 0, y = {1 .. 4}], 
      each [x] < 100, 
      each [
        x = List.Count([y]), 
        z = 
          let
            a = List.LastN([y], 4), 
            b = {a{0} + a{2}, a{1} + a{3}}
          in
            b, 
        y = [y] & z
      ], 
      each [y]
    )
  )
in
  Sol

Power Query solution 4 for Custom Sequence from Four Terms, proposed by An Nguyen:

Power Query M : List.Accumulate({5..100}, {1..4}, (s,c) => s & {s{c-5} + s{c-3}} )

Power Query solution 5 for Custom Sequence from Four Terms, proposed by Ramiro Ayala Chávez:

let
  a = List.Generate(
    () => [i = {1 .. 4}, j = 0], 
    each [j] < 50, 
    each [
      i = [i]
        & {List.Sum(List.Alternate(List.LastN([i], 4), 1, 1, 1))}
        & {List.Sum(List.Alternate(List.LastN([i], 4), 1, 1))}, 
      j = [j] + 1
    ], 
    each [i]
  ), 
  Sol = Table.FromColumns({List.FirstN(List.Last(a), 100)}, {"Answer Expected"})
in
  Sol

Power Query solution 6 for Custom Sequence from Four Terms, proposed by Seokho MOON:

let
  Fun = (n as number) =>
    List.Accumulate(
      {5 .. n}, 
      {1, 2, 3, 4}, 
      (a, v) => a & {List.LastN(a, 4){0} + List.LastN(a, 4){2}}
    )
in
  Fun(100)

Power Query solution 7 for Custom Sequence from Four Terms, proposed by Meganathan Elumalai:

let
  Source = Table.FromList(
    List.Accumulate(
      {1 .. 48}, 
      {1, 2, 3, 4}, 
      (s, c) =>
        [fx = (n) => List.Sum(List.Alternate(List.LastN(s, 4), 1, 1, n)), fin = s & {fx(1), fx(0)}][
          fin
        ]
    ), 
    each {_}, 
    {"Result"}
  )
in
  Source

Power Query solution 8 for Custom Sequence from Four Terms, proposed by Antriksh Sharma:

let a = List.LastN ( s, 4 ), b = { List.Sum ( List.Alternate ( a, 1, 1, 1 ) ) } & { List.Sum ( List.Alternate ( a, 1, 1, 0 ) ) } in s & b )

Power Query solution 9 for Custom Sequence from Four Terms, proposed by Peter Krkos:

PowerQuery solution:
= List.Last(List.Generate(
 ()=> [ a = {1,2,3,4}, b = {a{0}+a{2}, a{1}+a{3}}, c = a & b ],
 each List.Count([c]) <= 100,
 each [ a = List.LastN([c], 4), b = {a{0}+a{2}, a{1}+a{3}}, c = [c] & b ],
 each [c]))

Power Query solution 10 for Custom Sequence from Four Terms, proposed by Aleksandar Kovacevic:

let
  A = List.Generate(
    () => [a = 1, b = 2, c = 3, d = 4, x = 1], 
    each [x] < 50, 
    each [a = [c], b = [d], c = [a] + [c], d = [b] + [d], x = [x] + 1], 
    each [[c], [d]]
  ), 
  B = Table.FromColumns(
    {{1, 2} & List.Combine(List.Transform(A, each Record.ToList(_)))}, 
    {"Answer Expected"}
  )
in
  B

Solving the challenge of Custom Sequence from Four Terms with Excel

Excel solution 1 for Custom Sequence from Four Terms, proposed by Bo Rydobon 🇹🇭:

=REDUCE(
    {1;2;3;4},
    SEQUENCE(
        96
    ),
    LAMBDA(
        a,
        v,
        VSTACK(
            a,
            SUM(
                LARGE(
                    a,
                    {2,
                    4}
                )
            )
        )
    )
)

Excel solution 2 for Custom Sequence from Four Terms, proposed by Rick Rothstein:

=REDUCE(
    {1;2;3;4},
    SEQUENCE(
        48
    ),
    LAMBDA(
        a,
        x,
        VSTACK(
            a,
            MAP(
                {0;1},
                LAMBDA(
                    n,
                    SUM(
                        INDEX(
                            TAKE(
                                a,
                                -4
                            ),
                            {1;3}+n
                        )
                    )
                )
            )
        )
    )
)

Note: The semi-colon (;)

Excel solution 3 for Custom Sequence from Four Terms, proposed by John V.:

=REDUCE(ROW(1:4),ROW(1:96),LAMBDA(a,v,VSTACK(a,SUM(INDEX(a,{0;2}+v)))))

Excel solution 4 for Custom Sequence from Four Terms, proposed by 🇰🇷 Taeyong Shin:

=LET(R,LAMBDA(R,x,IF(ROWS(x)<100,R(R,VSTACK(x,MMULT(WRAPCOLS(TAKE(x,-4),2),{1;1}))),x)),R(R,{1;2;3;4}))

Excel solution 5 for Custom Sequence from Four Terms, proposed by Kris Jaganah:

Excel solution 6 for Custom Sequence from Four Terms, proposed by Alejandro Campos:

=[1,2,3,4]
while len(s)

Excel solution 7 for Custom Sequence from Four Terms, proposed by Timothée BLIOT:

=REDUCE({1;2;3;4},SEQUENCE(48),LAMBDA(w,v,VSTACK(w,BYROW(WRAPCOLS(TAKE(w,-4),2),SUM))))

Excel solution 8 for Custom Sequence from Four Terms, proposed by Hussein SATOUR:

=REDUCE(,SEQUENCE(100),LAMBDA(x,y,VSTACK(x,IF(y<5,y,SUM(CHOOSEROWS(x,-2,-4))))))

Excel solution 9 for Custom Sequence from Four Terms, proposed by Oscar Mendez Roca Farell:

=REDUCE(
    ROW(
        1:4
    ),
    ROW(
        1:48
    ),
    LAMBDA(
        i,
        x,
        VSTACK(
            i,
            BYROW(
                WRAPCOLS(
                    TAKE(
                        i,
                        -4
                    ),
                    2
                ),
                SUM
            )
        )
    )
)

Excel solution 10 for Custom Sequence from Four Terms, proposed by Duy Tùng:

=REDUCE(ROW(1:4),ROW(1:100),LAMBDA(x,y,VSTACK(x,SUM(INDEX(x,y+{0,2})))))

Excel solution 11 for Custom Sequence from Four Terms, proposed by Sunny Baggu:

=REDUCE(
    
     SEQUENCE(
         4
     ),
    
     SEQUENCE(
         100 / 2 - 2
     ),
    
     LAMBDA(
         a,
          v,
         
          VSTACK(
              
               a,
              
               BYROW(
                   
                    WRAPCOLS(
                        TAKE(
                            a,
                             -4
                        ),
                         2
                    ),
                   
                    LAMBDA(
                        b,
                         SUM(
                             b
                         )
                    )
                    
               )
               
          )
          
     )
    
)

Excel solution 12 for Custom Sequence from Four Terms, proposed by LEONARD OCHEA 🇷🇴:

=REDUCE({1;2;3;4},SEQUENCE(48),LAMBDA(a,b,VSTACK(a,MMULT({1,0,1,0;0,1,0,1},TAKE(a,-4)))))

Excel solution 13 for Custom Sequence from Four Terms, proposed by Anshu Bantra:

= [1, 2, 3, 4]
while len(sequence) < 100:
 last_four = sequence[-4:]
 sequence.extend([last_four[0] + last_four[2], last_four[1] + last_four[3]])

Excel solution 14 for Custom Sequence from Four Terms, proposed by Pieter de B.:

Excel solution 15 for Custom Sequence from Four Terms, proposed by Dhaval Patel:

Excel solution 16 for Custom Sequence from Four Terms, proposed by ferhat CK:

=TEXTSPLIT(REDUCE(ARRAYTOTEXT(SEQUENCE(4)),SEQUENCE(96,,4),LAMBDA(x,y,x&"; "&SUM(--CHOOSECOLS(TEXTSPLIT(x,"; "),y-1,y-3)))),,"; ")

=REDUCE(SEQUENCE(4),SEQUENCE(96,,4),LAMBDA(x,y,VSTACK(TAKE(x,y),SUM(INDEX(TAKE(x,-3),{1,3})))))

Excel solution 17 for Custom Sequence from Four Terms, proposed by Jaroslaw Kujawa:

=REDUCE(SEQUENCE(4);SEQUENCE(48);LAMBDA(a;x;LET(y;TAKE(a;-4;);VSTACK(a;SUM(CHOOSEROWS(y;{1;3}));SUM(CHOOSEROWS(y;{2;4}))))))

Excel solution 18 for Custom Sequence from Four Terms, proposed by Seokho MOON:

=REDUCE({1;2;3;4},SEQUENCE(96),LAMBDA(a,v,VSTACK(a,SUM(INDEX(TAKE(a,-4),{1,3})))))

Excel solution 19 for Custom Sequence from Four Terms, proposed by Ankur Sharma:

=REDUCE(VSTACK(1, 2, 3, 4), SEQUENCE(48), LAMBDA(iv,ar,
VSTACK(iv,
SUM(INDEX(iv, COUNT(iv) - 4 + 1), INDEX(iv, COUNT(iv) - 4 + 3)),
SUM(INDEX(iv, COUNT(iv) - 4 + 2), INDEX(iv, COUNT(iv) - 4 + 4)))))

Excel solution 20 for Custom Sequence from Four Terms, proposed by Meganathan Elumalai:

=REDUCE({1;2;3;4},SEQUENCE(48),LAMBDA(a,v,VSTACK(a,BYROW(INDEX(TAKE(a,-4),{1,3;2,4}),SUM))))

Excel solution 21 for Custom Sequence from Four Terms, proposed by Guillermo Arroyo:

=REDUCE(, SEQUENCE(100), LAMBDA(i, j, VSTACK(i, IF(j<5, j, SUM(INDEX(i, j-{4,2}))))))

Excel solution 22 for Custom Sequence from Four Terms, proposed by Maciej Kopczyński:

=TAKE(
 SCAN(1,
     SEQUENCE(
         99,
          1,
          2,
          1
     ),
    
 LAMBDA(state,
     curr,
    
 IF(curr <= 4,
    
 state & ";" & curr,
    
 state & ";" & LET(
 last4,
     TEXTSPLIT(
         state,
          ";"
     ),
    
 _1,
     CHOOSECOLS(
         last4,
          -4
     ),
    
 _2,
     CHOOSECOLS(
         last4,
          -3
     ),
    
 _3,
     CHOOSECOLS(
         last4,
 &         -2
     ),
    
 _4,
     CHOOSECOLS(
         last4,
          -1
     ),
    
 result,
     (_1 + _3) & ";" & (_2 + _4),
    
 result
 )
 )
 )
 ,
     -1)
)

Excel solution 23 for Custom Sequence from Four Terms, proposed by Erdit Qendro:

=LET(
    iAr,
    {1;2;3;4},
    maxR,
    100,
    
    s,
    SUM,
    
    vs,
    VSTACK,
    
    c,
    CHOOSEROWS,
    
    lrc,
    LAMBDA(
        lrc,
        a,
        
        IF(
            ROWS(
                a
            )>=maxR,
            a,
            lrc(
                lrc,
                vs(
                    a,
                    LET(
                        ar,
                        TAKE(
                            a,
                            -4
                        ),
                        
                        vs(
                            s(
                                c(
                                    ar,
                                    1,
                                    3
                                )
                            ),
                            s(
                                c(
                                    ar,
                                    2,
                                    4
                                )
                            )
                        )
                    )
                )
            )
        )
    ),
    
    lrc(
        lrc,
        iAr
    )
)

Excel solution 24 for Custom Sequence from Four Terms, proposed by Cary Ballard, DML:

=REDUCE(
    SEQUENCE(
        4
    ),
     SEQUENCE(
         50
     ),
     LAMBDA(
         a,
         v,
          VSTACK(
              a,
               SUM(
                   CHOOSEROWS(
                       TAKE(
                           a,
                           -4
                       ),
                       1,
                       3
                   )
               ),
               SUM(
                   CHOOSEROWS(
                       TAKE(
                           a,
                           -4
                       ),
                       2,
                       4
                   )
               )
          )
     )
)
or
=LET(
    f,
     LAMBDA(
         x,
          y,
          z,
          SUM(
              CHOOSEROWS(
                  TAKE(
                      x,
                       -4
                  ),
                   y,
                   z
              )
          )
     ),
     REDUCE(
         SEQUENCE(
        4
    ),
          SEQUENCE(
         50
     ),
          LAMBDA(
              a,
               v,
               VSTACK(
                   a,
                    f(
                        a,
                         1,
                         3
                    ),
                    f(
                        a,
                         2,
                         4
                    )
               )
          )
     )
)

Excel solution 25 for Custom Sequence from Four Terms, proposed by LUIS FLORENTINO COUTO CORTEGOSO:

=REDUCE(ROW(1:4),ROW(5:100),LAMBDA(a,x,VSTACK(a,SUM(INDEX(a,x-{2;4},)))))

Excel solution 26 for Custom Sequence from Four Terms, proposed by Fredson Alves Pinho:

=LET(f,LAMBDA(n,LAMBDA(i,INDEX(n,1,i))),
x,SCAN(0,SEQUENCE(50),LAMBDA(a,v,f(IF(v=1,{2,2,1,2},HSTACK(a(3),a(4),a(1)+a(3),a(2)+a(4)))))),TOCOL(HSTACK(x(3),x(4))))

Excel solution 27 for Custom Sequence from Four Terms, proposed by Craig Runciman:

=REDUCE({1;2;3;4},SEQUENCE(48),LAMBDA(a,v,LET(r,TAKE(a,-4),VSTACK(a,SUM(INDEX(r,{1;3})),SUM(INDEX(r,{2;4}))))))

Excel solution 28 for Custom Sequence from Four Terms, proposed by Ricardo Romero Garcia:

=REDUCE({1,2,3,4},SEQUENCE(96),LAMBDA(a,v,VSTACK(a,SUM(INDEX(TAKE(a,-4),{1,3})))))

Excel solution 29 for Custom Sequence from Four Terms, proposed by Luis Enrique Charca Ponce:

=--TEXTBEFORE(SCAN("2,1,2,3",SEQUENCE(100),LAMBDA(ac,v,
LET(arr,TEXTSPLIT(ac,","),TEXTAFTER(ac,",")&","&(--INDEX(arr,1)--INDEX(arr,3))))),",")

Solving the challenge of Custom Sequence from Four Terms with Python

Python solution 1 for Custom Sequence from Four Terms, proposed by Konrad Gryczan, PhD:

import pandas as pd
path = "693 Generate a sequence.xlsx"
test = pd.read_excel(path, usecols="A", nrows=101)
start = [1, 2, 3, 4]
while len(start) < 101:
 start += [start[-4] + start[-2], start[-3] + start[-1]]
start = start[:100]
df = pd.DataFrame({'Sequence': start})
print(df["Sequence"].equals(test["Answer Expected"])) # True

Python solution 2 for Custom Sequence from Four Terms, proposed by Ernesto Vega Castillo:

def secuencia_num(total_num):
 secuencia = [1, 2, 3, 4]
 for i in range(4, total_num):
 nuevo_num = secuencia[i - 4] + secuencia[i - 2]
 secuencia.append(nuevo_num)
 return secuencia
secuencia_100 = secuencia_num(100)
print("Answer Expected")
for numero in secuencia_100:
 print(numero)

Solving the challenge of Custom Sequence from Four Terms with Python in Excel

Python in Excel solution 1 for Custom Sequence from Four Terms, proposed by Aditya Kumar Darak 🇮🇳:

from itertools import islice
def MyFun(n):
 a, b, c, d = 1, 2, 3, 4
 yield a
 yield b
 yield c
 yield d
 for _ in range((n - 4) // 2):
 next1 = a + c
 next2 = b + d
 yield next1
 yield next2
 a, b, c, d = c, d, next1, next2
result = list(MyFun(100))
result

Solving the challenge of Custom Sequence from Four Terms with R

R solution 1 for Custom Sequence from Four Terms, proposed by Konrad Gryczan, PhD:

library(tidyverse)
library(readxl)
path = "Excel/693 Generate a sequence.xlsx"
test  = read_excel(path, range = "A1:A101")
start = list(1, 2, 3, 4)
for (i in 1:50) {
 start = append(start, c(sum(start[[length(start) - 3]], start[[length(start) - 1]]),
 sum(start[[length(start) - 2]], start[[length(start)]])))
}
df = data.frame(a = unlist(start[1:100]))
all.equal(df$a, test$`Answer Expected`)
# TRUE

Solving the challenge of Custom Sequence from Four Terms with Excel VBA

Excel VBA solution 1 for Custom Sequence from Four Terms, proposed by Ankur Sharma:

My VBA attempt:
Sub ExcelBI_Challenge()
Dim NumArray, NumLoop As Integer
ReDim NumArray(1 To 4)
NumArray(1) = 1
NumArray(2) = 2
NumArray(3) = 3
NumArray(4) = 4
For NumLoop = 5 To 99 Step 2
 
ReDim Preserve NumArray(LBound(NumArray) To UBound(NumArray) + 2)
NumArray(UBound(NumArray) - 1) = NumArray(NumLoop - 4) + NumArray(NumLoop - 2)
NumArray(UBound(NumArray)) = NumArray(NumLoop - 3) + NumArray(NumLoop - 1)
 
Next NumLoop
Sheet1.Range("A2").Resize(UBound(NumArray), 1).Value = Application.Transpose(NumArray)
End Sub
Best Wishes!