Generate n Fibonacci strings for given first two terms. In Fibonacci numbers, term Tn = T(n-2)+T(n-1) In the same way, in Fibonacci strings Tn = T(n-2) & T(n-1) where & is the concatenation operator. Hence, if first two terms are s & t then Fibonacci strings will be s, t, st, tst, sttst and so on. Note – In the last row, I have taken the output in 2 lines to save space. You have to get the output in a single line only. Note- C4 is 6 which I have updated in Excel (Picture once inserted can’t be updated in Linkedin)
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 104
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Generate Fibonacci Strings with Power Query
Power Query solution 1 for Generate Fibonacci Strings, proposed by Bhavya Gupta:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
StrFib = (Rec) =>
let
F = Record.ToList(Rec),
F_1 = Text.From(F{0}),
F_2 = Text.From(F{1}),
C = F{2},
Fib = Text.Combine(
List.FirstN(
{F_1, F_2}
& List.Generate(
() => [x = F_1, y = F_2, z = 2],
each [z] < C,
each [x = [y], y = [x] & [y], z = [z] + 1],
each [x] & [y]
),
C
),
", "
)
in
Fib,
ExpectedOutput = Table.AddColumn(Source, "FibStr", each StrFib(_))
in
ExpectedOutputPower Query solution 2 for Generate Fibonacci Strings, proposed by Matthias Friedmann:
let
Source = Excel.CurrentWorkbook(){[Name = "Fibonacci"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"First", type text}, {"Second", type text}}
),
#"Added Custom" = Table.AddColumn(
#"Changed Type",
"Fibonacci",
each List.Generate(
() => [a = [First], b = [Second], c = a & b, d = a & ", " & b & ", " & c, i = 0, n = [n]],
each [i] < [n] - 2,
each [a = [b], b = [c], c = a & b, d = [d] & ", " & c, i = [i] + 1, n = [n]],
each [d]
){[n] - 3}
)
in
#"Added Custom"Solving the challenge of Generate Fibonacci Strings with Excel
Excel solution 1 for Generate Fibonacci Strings, proposed by Bo Rydobon 🇹🇭:
=BYROW(
A2:C6,
LAMBDA(
r,
ARRAYTOTEXT(
DROP(
REDUCE(
TAKE(
r,
,
2
),
SEQUENCE(
DROP(
r,
,
2
)
),
LAMBDA(
a,
n,
HSTACK(
a,
CONCAT(
TAKE(
a,
,
-2
)
)
)
)
),
,
-2
)
)
)
)Excel solution 2 for Generate Fibonacci Strings, proposed by John V.:
=MAP(
A2:A6,
B2:B6,
C2:C6,
LAMBDA(
f,
s,
n,
LET(
u,
", ",
REDUCE(
f&u&s,
SEQUENCE(
n-2
),
LAMBDA(
i,
x,
i&u&SUBSTITUTE(
TEXTAFTER(
u&i,
u,
-2
),
u,
)
)
)
)
)
)Excel solution 3 for Generate Fibonacci Strings, proposed by محمد حلمي:
=MAP(
A2:A6,
B2:B6,
C2:C6,
LAMBDA(
a,
b,
c,
TEXTBEFORE(
ARRAYTOTEXT(
REDUCE(
a&"-"&b&"-"&a&b,
SEQUENCE(
c-2
),
LAMBDA(
a,
d,
LET(
e,
TEXTSPLIT(
a,
,
"-"
),
v,
CONCAT(
INDEX(
e,
ROWS(
e
)-1
)&TAKE(
e,
-1
)
),
VSTACK(
e,
v
)
)
)
)
),
", ",
-1
)
)
)Excel solution 4 for Generate Fibonacci Strings, proposed by محمد حلمي:
=BYROW(
A2:B6,
LAMBDA(
a,
ARRAYTOTEXT(
REDUCE(
a,
SEQUENCE(
INDEX(
C2:C6,
ROW(
a
)-1
)-2
),
LAMBDA(
x,
y,
HSTACK(
x,
DROP(
TAKE(
x,
,
-2
),
,
-1
)&TAKE(
x,
,
-1
)
)
)
)
)
)
)Excel solution 5 for Generate Fibonacci Strings, proposed by محمد حلمي:
=BYROW(
A2:B6,
LAMBDA(
a,
ARRAYTOTEXT(
REDUCE(
a,
SEQUENCE(
INDEX(
C2:C6,
ROW(
a
)-1
)-2
),
LAMBDA(
x,
y,
HSTACK(
x,
DROP(
TAKE(
x,
,
-2
),
,
-1
)&TAKE(
x,
,
-1
)
)
)
)
)
)
)Excel solution 6 for Generate Fibonacci Strings, proposed by محمد حلمي:
=MAP(
A2:A6,
B2:B6,
C2:C6,
LAMBDA(
a,
b,
c,
ARRAYTOTEXT(
REDUCE(
a&"-"&b,
SEQUENCE(
c-2
),
LAMBDA(
a,
d,
LET(
e,
TEXTSPLIT(
a,
,
"-"
),
v,
CONCAT(
INDEX(
e,
ROWS(
e
)-1
)&TAKE(
e,
-1
)
),
VSTACK(
e,
v
)
)
)
)
)
)
)Excel solution 7 for Generate Fibonacci Strings, proposed by 🇰🇷 Taeyong Shin:
=LET(
FIBO, LAMBDA(char_1,char_2,num,
LET(
Loop, LAMBDA(ME,a,b,n,
IF(n = 1, a, IF(n = 2, b, ME(ME, a, b, n - 2) & ME(ME, a, b, n - 1)))
),
Loop(Loop, char_1, char_2, num)
)
),
JoinStr, LAMBDA(Me,char_1,char_2,n,Exit,
LET(
Result, FIBO(char_1, char_2, n),
IF(n = Exit, Result, Result & ", " & Me(Me, char_1, char_2, n + 1, Exit))
)
),
MAP(A2:A6, B2:B6, C2:C6, LAMBDA(a,b,c, JoinStr(JoinStr, a, b, 1, c) ))
)
**********************************************************************
You can reduce the number of parameter passes by putting the helper function outside the LET variable.
=MAP(A2:A6, B2:B6, C2:C6, LAMBDA(a,b,c,
LET(
FIBO, LAMBDA(num,
LET(
Loop, LAMBDA(Me,n,
IF(n = 1, a, IF(n = 2, b, Me(Me, n - 2) & Me(Me, n - 1)))
),
Loop(Loop, num)
)
),
JoinStr, LAMBDA(ME,n,
LET(
Result, FIBO(n),
IF(n = c, Result, Result & ", " & ME(ME, n + 1))
)
),
JoinStr(JoinStr, 1)
)
))Excel solution 8 for Generate Fibonacci Strings, proposed by Julian Poeltl:
=MAP(
A2:A6,
B2:B6,
C2:C6,
LAMBDA(
F,
S,
N,
REDUCE(
F&", "&S,
SEQUENCE(
N-2
),
LAMBDA(
A,
B,
TEXTJOIN(
", ",
,
A,
CONCAT(
TAKE(
TEXTSPLIT(
A,
", "
),
,
-2
)
)
)
)
)
)
)Excel solution 9 for Generate Fibonacci Strings, proposed by Aditya Kumar Darak 🇮🇳:
=LET(
_fi,
A2:A6,
_se,
B2:B6,
_n,
C2:C6,
_e1,
LAMBDA(
x,
y,
VSTACK(
x,
DROP(
TAKE(
x,
-2
),
-1
) & TAKE(
x,
-1
)
)
),
_e2,
LAMBDA(
a,
b,
c,
ARRAYTOTEXT(
REDUCE(
VSTACK(
a,
b
),
SEQUENCE(
c - 2
),
_e1
)
)
),
_r,
MAP(
_fi,
_se,
_n,
_e2
),
_r
)Excel solution 10 for Generate Fibonacci Strings, proposed by Aditya Kumar Darak 🇮🇳:
=LET(
_fi,
A2:A6,
_se,
B2:B6,
_n,
C2:C6,
_e1,
LAMBDA(
x,
y,
x & ", " & CONCAT(
TAKE(
TEXTSPLIT(
x,
,
", "
),
-2
)
)
),
_e2,
LAMBDA(
a,
b,
c,
REDUCE(
a & ", " & b,
SEQUENCE(
c
),
_e1
)
),
_f,
MAP(
_fi,
_se,
_n,
_e2
),
_r,
TEXTBEFORE(
_f,
", ",
_n
),
_r
)
Way2:
=LET(
_fi,
A2:A6,
_se,
B2:B6,
_n,
C2:C6,
_e1,
LAMBDA(
x,
y,
VSTACK(
x,
DROP(
TAKE(
x,
-2
),
-1
) & TAKE(
x,
-1
)
)
),
_e2,
LAMBDA(
a,
b,
c,
ARRAYTOTEXT(
TAKE(
REDUCE(
VSTACK(
a,
b
),
SEQUENCE(
c
),
_e1
),
c
)
)
),
_r,
MAP(
_fi,
_se,
_n,
_e2
),
_r
)Excel solution 11 for Generate Fibonacci Strings, proposed by Timothée BLIOT:
=MAP(
A2:A6,
B2:B6,
C2:C6,
LAMBDA(
a,
b,
c,
LET(
F,
LAMBDA(
me,
n,
x,
y,
IF(
n=1,
x,
IF(
n=2,
y,
me(
me,
n-1,
y,
x&y
)
)
)
),
ARRAYTOTEXT(
REDUCE(
a,
SEQUENCE(
c-1,
,
2
),
LAMBDA(
ac,
v,
VSTACK(
ac,
F(
F,
v,
a,
b
)
)
)
)
)
)
)
)Excel solution 12 for Generate Fibonacci Strings, proposed by Bhavya Gupta:
=MAP(
A2:A6,
B2:B6,
C2:C6,
LAMBDA(
f,
s,
n,
ARRAYTOTEXT(
TAKE(
TEXTSPLIT(
REDUCE(
f&", "&s,
SEQUENCE(
n
),
LAMBDA(
x,
y,
x&", "&CONCAT(
TAKE(
TEXTSPLIT(
x,
,
", "
),
-2
)
)
)
),
,
& ", "
),
n
)
)
)
)Excel solution 13 for Generate Fibonacci Strings, proposed by Md. Zohurul Islam:
=MAP(
A2:A6,
B2:B6,
C2:C6,
LAMBDA(
x,
y,
n,
LET(
u,
VSTACK(
x,
y
),
v,
SEQUENCE(
IF(
n<=2,
1,
n-2
)
),
w,
REDUCE(
u,
v,
LAMBDA(
p,
q,
LET(
a,
INDEX(
p,
ROWS(
p
)-1
),
b,
INDEX(
p,
ROWS(
p
)
),
d,
CONCAT(
a,
b
),
e,
VSTACK(
p,
d
),
e
)
)
),
z,
ARRAYTOTEXT(
w
),
z
)
)
)Excel solution 14 for Generate Fibonacci Strings, proposed by Charles Roldan:
=LET(
f,
LAMBDA(
t,
HSTACK(
t,
CONCAT(
TAKE(
t,
,
-2
)
)
)
),
g,
LAMBDA(
u,
x,
k,
IF(
k = 2,
x,
u(
u,
f(
x
),
k - 1
)
)
),
h,
LAMBDA(
a,
b,
n,
ARRAYTOTEXT(
g(
g,
HSTACK(
a,
b
),
n
)
)
),
MAP(
A2:A6,
B2:B6,
C2:C6,
h
)
)Excel solution 15 for Generate Fibonacci Strings, proposed by Stefan Olsson:
=MAP(
A2:A6,
B2:B6,
C2:C6,
LAMBDA(
f,
s,
n,
IF(
n<3,
f&", "&s,
REDUCE(
f&", "&s,
SEQUENCE(
n-2
),
LAMBDA(
fs ,
m,
REGEXREPLACE(
fs,
"^(.*?(w+), (w+))$",
"$1, $2$3"
)
)
)
)
)
)Excel solution 16 for Generate Fibonacci Strings, proposed by Abhishek Kumar Jain:
=MAP(
A2:A6,
B2:B6,
C2:C6,
LAMBDA(
a,
b,
c,
IF(
c<3,
a&", "&b,
LET(
d,
SCAN(
a&", "&b,
SEQUENCE(
c-2
),
LAMBDA(
x,
y,
x&", "&INDEX(
TEXTSPLIT(
x,
", "
),
,
COUNTA(
TEXTSPLIT(
x,
", "
)
)-1
)&INDEX(
TEXTSPLIT(
x,
", "
),
,
COUNTA(
TEXTSPLIT(
x,
", "
)
)
)
)
),
INDEX(
d,
COUNTA(
d
)
)
)
)
)
)Excel solution 17 for Generate Fibonacci Strings, proposed by Abdelrahman Omer, MBA, PMP:
=MAP(
A2:A6,
B2:B6,
C2:C6,
LAMBDA(
x,
y,
z,
ARRAYTOTEXT(
LET(
a,
VSTACK(
x,
y
),
REDUCE(
a,
SEQUENCE(
z-2
),
LAMBDA(
v,
r,
VSTACK(
v,
CONCAT(
TAKE(
v,
-2
)
)
)
)
)
)
)
)
)Solving the challenge of Generate Fibonacci Strings with SQL
SQL solution 1 for Generate Fibonacci Strings, proposed by Zoran Milokanović:
WITH /* Microsoft SQL Server 2019 */
DATA_PREP
AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY T.TEMP) AS ORDINAL_NUMBER
,T.N
FROM
(
SELECT
1 AS TEMP
,D.FIRST
,D.SECOND
,D.N
FROM DATA D
) T
),
FIBONACCI
AS
(
SELECT
DP.ORDINAL_NUMBER
,DP.FIRST
,DP.SECOND
,DP.N
,DP.N AS CALC_N
FROM DATA_PREP DP
UNION ALL
SELECT
F.ORDINAL_NUMBER
,F.FIRST
,F.SECOND
,F.N
,F.CALC_N - 1 AS CALC_N
FROM FIBONACCI F
WHERE
F.CALC_N > 1
)
SELECT
F.FIRST
,F.SECOND
,F.N
,STRING_AGG(F.CALC_FIRST, ', ') WITHIN GROUP(ORDER BY F.CALC_N DESC) AS ANSWER
FROM FIBONACCI F
GROUP BY
F.ORDINAL_NUMBER
,F.FIRST
,F.SECOND
,F.N
ORDER BY
F.ORDINAL_NUMBER
;
&&