Generate the result table. Here, Tn = T(n-2) & Tn where & is concatenation operator.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 313
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Generate the result table with Tn = T(n-2) & Tn with Power Query
Power Query solution 1 for Generate the result table with Tn = T(n-2) & Tn, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.FromRows(
List.Transform(
Table.ToRows(Source),
each
let
T = Text.From
in
List.Accumulate(
List.Skip(_, 2),
List.FirstN(_, 2),
(s, c) => s & {T(List.Reverse(s){1}) & T(c)}
)
)
)
in
SPower Query solution 2 for Generate the result table with Tn = T(n-2) & Tn, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Col = Table.FromRows(
Table.AddColumn(
Source,
"A",
each List.RemoveNulls(
let
z = List.Transform(Record.ToList(_), Text.From),
a = {List.Alternate(z, 1, 1, 1)} & {List.Alternate(z, 1, 1, 0)},
b = List.Transform(a, Text.Combine),
c = List.Combine(
List.Zip(
List.Transform(
b,
each List.Transform({1 .. Text.Length(_)}, (x) => Text.Range(_, 0, x))
)
)
)
in
c
)
)[A]
)
in
ColPower Query solution 3 for Generate the result table with Tn = T(n-2) & Tn, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
res = Table.Combine(
Table.AddColumn(
Fonte,
"Personalizar",
each [
a = List.Transform(Record.FieldValues(_), Text.From),
b = List.Transform(
{1 .. List.Count(a)},
(x) =>
[
o = List.FirstN(List.Transform(a, Text.From), x - 2),
p = try
if Number.IsEven(List.Count(o)) then
List.Alternate(o, 1, 1)
else
List.Alternate(o, 1, 1, 1)
otherwise
{}
][p]
),
c = Table.FromRows(
{List.Transform(List.Zip({List.Transform(b, Text.Combine), a}), Text.Combine)}
)
][c]
)[Personalizar]
)
in
resPower Query solution 4 for Generate the result table with Tn = T(n-2) & Tn, proposed by Ramiro Ayala Chávez:
let
Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content],
a = Table.ToRows(Origen),
b = List.Transform(a, each List.Alternate(_, 1, 1, 1)),
c = List.Transform(a, each List.Alternate(_, 1, 1)),
d = List.Transform(b, each List.RemoveLastN(_)),
e = List.Transform(c, each List.RemoveLastN(_)),
f = List.Transform(d, each List.RemoveLastN(_)),
g = List.Transform(e, each List.RemoveLastN(_)),
h = List.Transform(f, each List.RemoveLastN(_)),
i = Table.FromColumns({h, g, f, e, d, c, b}),
Sol = Table.TransformColumns(
i,
{
{"Column1", each Text.Combine(List.Transform(_, Text.From))},
{"Column2", each Text.Combine(List.Transform(_, Text.From))},
{"Column3", each Text.Combine(List.Transform(_, Text.From))},
{"Column4", each Text.Combine(List.Transform(_, Text.From))},
{"Column5", each Text.Combine(List.Transform(_, Text.From))},
{"Column6", each Text.Combine(List.Transform(_, Text.From))},
{"Column7", each Text.Combine(List.Transform(_, Text.From))}
}
)
in
SolSolving the challenge of Generate the result table with Tn = T(n-2) & Tn with Excel
Excel solution 1 for Generate the result table with Tn = T(n-2) & Tn, proposed by Bo Rydobon 🇹🇭:
=DROP(
REDUCE(
0,
SEQUENCE(
7
),
LAMBDA(
a,
v,
HSTACK(
a,
IFERROR(
CHOOSECOLS(
a,
-2
),
""
)&INDEX(
A2:G5,
,
v
)
)
)
),
,
1
)Excel solution 2 for Generate the result table with Tn = T(n-2) & Tn, proposed by Rick Rothstein:
=TEXTSPLIT(
TEXTJOIN(
"/",
,
BYROW(
A2:G5,
LAMBDA(
r,
LET(
s,
SEQUENCE(
,
7
),
e,
SCAN(
"",
s,
LAMBDA(
a,
x,
a&IF(
ISEVEN(
x
),
INDEX(
r,
,
x
),
""
)
)
),
o,
SCAN(
"",
s,
LAMBDA(
a,
x,
a&IF(
ISODD(
x
),
INDEX(
r,
,
x
),
""
)
)
),
TRIM(
REDUCE(
"",
s,
LAMBDA(
a,
x,
a&" "&IF(
ISODD(
x
),
INDEX(
o,
,
x
),
INDEX(
e,
,
x
)
)
)
)
)
)
)
)
),
" ",
"/"
)Excel solution 3 for Generate the result table with Tn = T(n-2) & Tn, proposed by John V.:
=MAKEARRAY(4,
7,
LAMBDA(r,
c,
CONCAT(INDEX(A2:G5,
r,
2*SEQUENCE(,
(1+c)/2)-ISODD(
c
)))))Excel solution 4 for Generate the result table with Tn = T(n-2) & Tn, proposed by محمد حلمي:
=DROP(
REDUCE(
0,
A2:A5,
LAMBDA(
C,
V,
VSTACK(
C,
REDUCE(
OFFSET(
V,
,
,
,
2
),
OFFSET(
V,
,
2,
,
5
),
LAMBDA(
A,
D,
HSTACK(
A,
TAKE(
DROP(
A,
,
-1
),
,
-1
)&D
)
)
)
)
)
),
1
)Excel solution 5 for Generate the result table with Tn = T(n-2) & Tn, proposed by محمد حلمي:
= INDEX(
A,
COLUMNS(
A
)-1
)&D
=INDEX(
A,
COLUMN(
D
)-2
)&D
A = accumulator
=REDUCE(
A2:B2,
C2:G2,
LAMBDA(
A,
D,
HSTACK(
A,
TAKE(
DROP(
A,
,
-1
),
,
-1
)&D
)
)
)
=REDUCE(
A2:B2,
C2:G2,
LAMBDA(
A,
D,
HSTACK(
A,
INDEX(
A,
COLUMNS(
A
)-1
)&D
)
)
)
=REDUCE(
A2:B2,
C2:G2,
LAMBDA(
A,
D,
HSTACK(
A,
INDEX(
A,
COLUMN(
D
)-2
)&D
)
)
)Excel solution 6 for Generate the result table with Tn = T(n-2) & Tn, proposed by محمد حلمي:
=MAKEARRAY(
4,
7,
LAMBDA(
r,
c,
CONCAT(
INDEX(
A2:G5,
r,
IF(
ISODD(
c
),
SEQUENCE(
c/2+1,
,
,
2
),
SEQUENCE(
c/2
)*2
)
)
)
)
)Excel solution 7 for Generate the result table with Tn = T(n-2) & Tn, proposed by Kris Jaganah:
=LET(
a,
A2:G5,
b,
SEQUENCE(
,
7
),
TEXTSPLIT(
TEXTJOIN(
"#",
,
BYROW(
a,
LAMBDA(
z,
ARRAYTOTEXT(
MAP(
z,
b,
LAMBDA(
x,
y,
CONCAT(
XLOOKUP(
y-{6,
4,
2},
b,
z,
""
)
)&x
)
)
)
)
)
),
", ",
"#"
)
)Excel solution 8 for Generate the result table with Tn = T(n-2) & Tn, proposed by Julian Poeltl:
=DROP(
LET(
A,
A2:G5,
REDUCE(
0,
SEQUENCE(
ROWS(
A
)
),
LAMBDA(
B,
C,
VSTACK(
B,
LET(
S,
SCAN(
"",
CHOOSEROWS(
A,
C
),
CONCAT
),
SQ,
SEQUENCE(
,
COLUMNS(
S
)
),
MAP(
SQ,
S,
LAMBDA(
A,
B,
CONCAT(
IF(
ISEVEN(
A
),
FILTER(
MID(
B,
SEQUENCE(
A
),
1
),
ISEVEN(
SEQUENCE(
A
)
)
),
FILTER(
MID(
B,
SEQUENCE(
A
),
1
),
ISODD(
SEQUENCE(
A
)
)
)
)
)
)
)
)
)
)
)
),
1
)Excel solution 9 for Generate the result table with Tn = T(n-2) & Tn, proposed by Timothée BLIOT:
=DROP(
REDUCE(
"",
SEQUENCE(
4
),
LAMBDA(
w,
v,
LET(
F,
LAMBDA(
n,
m,
FILTER(
n,
MOD(
SEQUENCE(
,
m
),
2
)
)
),
A,
F(
INDEX(
A2:G5,
v,
),
7
),
B,
F(
INDEX(
B2:G5,
v,
),
6
),
G,
LAMBDA(
n,
SCAN(
"",
n,
LAMBDA(
x,
y,
x&y
)
)
),
VSTACK(
w,
TOROW(
VSTACK(
G(
A
),
G(
B
)
),
3,
1
)
)
)
)
),
1
)Excel solution 10 for Generate the result table with Tn = T(n-2) & Tn, proposed by Hussein SATOUR:
=TEXTSPLIT(
CONCAT(
BYROW(
A2:G5,
LAMBDA(
z,
LET(
&
a,
TOCOL(
z
),
b,
SEQUENCE(
7
),
c,
VSTACK(
SCAN(
,
FILTER(
a,
MOD(
b,
2
)>0
),
LAMBDA(
x,
y,
x&y
)
),
SCAN(
,
FILTER(
a,
MOD(
b,
2
)=0
),
LAMBDA(
x,
y,
x&y
)
)
),
CONCAT(
TEXTJOIN(
"/",
,
SORTBY(
c,
LEN(
c
)
)
)
)&"|"
)
)
)
),
"/",
"|",
1
)Excel solution 11 for Generate the result table with Tn = T(n-2) & Tn, proposed by Sunny Baggu:
=DROP(
REDUCE(
"",
SEQUENCE(ROWS(A2:G5)),
LAMBDA(x, y,
VSTACK(
x,
DROP(
REDUCE(
"",
SEQUENCE(, 7),
LAMBDA(a, v,
HSTACK(
a,
LET(
_a, SEQUENCE(v, , v, -2),
CONCAT(INDEX(INDEX(A2:G5, y, ), SORT(FILTER(_a, _a > 0))))
)
)
)
),
,
1
)
)
)
),
1
)Excel solution 12 for Generate the result table with Tn = T(n-2) & Tn, proposed by Sunny Baggu:
=MAKEARRAY(
ROWS(
A2:G5
),
COLUMNS(
A2:G5
),
LAMBDA(
r,
c,
INDEX(
LET(
_seq,
SEQUENCE(
,
7
),
MAP(
_seq,
ROUNDUP(
_seq / 2,
0
),
LAMBDA(
a,
b,
CONCAT(
INDEX(
INDEX(
A2:G5,
r,
),
SORT(
SEQUENCE(
b,
,
a,
-2
)
)
)
)
)
)
),
c
)
)
)Excel solution 13 for Generate the result table with Tn = T(n-2) & Tn, proposed by Abdallah Ally:
=LET(
a,
A2:G5,
f,
LAMBDA(
v,
n,
IFERROR(
OFFSET(
v,
,
n
),
""
)
),
MAP(
a,
LAMBDA(
x,
CONCAT(
f(
x,
-6
),
f(
x,
-4
),
f(
x,
-2
),
x
)
)
)
)Excel solution 14 for Generate the result table with Tn = T(n-2) & Tn, proposed by Charles Roldan:
=LET(
Data,
A2:G5,
_Build,
LAMBDA(
a,
v,
VSTACK(
a,
TAKE(
TAKE(
a,
-2
),
1
) & v
)
),
_Gather,
LAMBDA(
x,
TEXTJOIN(
",",
,
DROP(
REDUCE(
{""; ""},
x,
_Build
),
2
)
)
),
TEXTSPLIT(
TEXTJOIN(
";",
,
BYROW(
Data,
_Gather
)
),
",",
";"
)
)Excel solution 15 for Generate the result table with Tn = T(n-2) & Tn, proposed by JvdV –:
=DROP(REDUCE(A2:B5,{2,4,6},LAMBDA(x,y,HSTACK(x,TAKE(x,,-2)&OFFSET(A2,,y,4,2)))),,-1)Excel solution 16 for Generate the result table with Tn = T(n-2) & Tn, proposed by Oscar Javier Rosero Jiménez:
=LET(
_a,
LAMBDA(
_x,
LET(
y,
_x,
ip,
--ISEVEN(
COLUMN(
y
)
),
impar,
SCAN(
,
FILTER(
y,
ip=0
),
LAMBDA(
i,
x,
i&x
)
),
par,
SCAN(
,
FILTER(
y,
ip
),
LAMBDA(
i,
x,
i&x
)
),
DROP(
TOROW(
VSTACK(
impar,
par
),
,
2
),
,
-1
)
)
),
c,
BYROW(
A2:G5,
LAMBDA(
_b,
TEXTJOIN(
",",
,
_a(
_b
)
)
)
),
TEXTSPLIT(
TEXTJOIN(
"-",
,
c
),
",",
"-"
)
)Excel solution 17 for Generate the result table with Tn = T(n-2) & Tn, proposed by Pieter de Bruijn:
=DROP(REDUCE(A2:B5,{2,4,6},LAMBDA(x,y,HSTACK(x,TAKE(x,,-2)&INDEX(C2:G5,{1;2;3;4},y+{0,1})))),,-1)
or a little longer, but more dynamical:
=LET(a,A2:G5,DROP(REDUCE(TAKE(a,,1),SEQUENCE(,INT(COLUMNS(a)/2),,2),LAMBDA(x,y,HSTACK(x,TAKE(x,,-2)&INDEX(DROP(a,,2),SEQUENCE(ROWS(a)),y+{0,1})))),,-1))Excel solution 18 for Generate the result table with Tn = T(n-2) & Tn, proposed by Victor Momoh (MVP, MOS, R.Eng):
=LET(
a,
A2:H5,
MAKEARRAY(
ROWS(
a
),
COLUMNS(
a
),
LAMBDA(
r,
c,
CONCAT(
INDEX(
a,
r,
SEQUENCE(
,
ROUND(
c/2,
0
),
,
2
)+ISEVEN(
c
)
)
)
)
)
)
hardcoding the number of rows and columns
=MAKEARRAY(
4,
7,
LAMBDA(
r,
c,
CONCAT(
INDEX(
A2:G5,
r,
SEQUENCE(
,
ROUND(
c/2,
0
),
,
2
)+ISEVEN(
c
)
)
)
)
)Excel solution 19 for Generate the result table with Tn = T(n-2) & Tn, proposed by Giorgi Goderdzishvili:
=LET(
grd,
A3:G6,
sq,
SEQUENCE(
,
7
),
mkr,
MAKEARRAY(4,
7,
LAMBDA(r,
c,
CONCAT(
FILTER(INDEX(
grd,
r,
0
),
(sq=c)+
IFERROR(ISNUMBER(XMATCH(sq,
SEQUENCE(,
INT((c-1)/2),
c-2,
-2),
0)),
0))))),
mkr)Solving the challenge of Generate the result table with Tn = T(n-2) & Tn with Python in Excel
Python in Excel solution 1 for Generate the result table with Tn = T(n-2) & Tn, proposed by John V.:
Hi everyone!
for r in range(4):
for c in range(7):
t[r][c] = ('' if c < 2 else t[r][c - 2]) + str(d[r][c])
t
Blessings!
Solving the challenge of Generate the result table with Tn = T(n-2) & Tn with R
R solution 1 for Generate the result table with Tn = T(n-2) & Tn, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
library(data.table)
input = read_excel("Scan3.xlsx", range = "A2:G5", col_names = c("X1", "X2", "X3", "X4", "X5", "X6", "X7"))
test = read_excel("Scan3.xlsx", range = "I2:O5", col_names = c("X1", "X2", "X3", "X4", "X5", "X6", "X7"))
process_columns <- function(df) {
num_cols <- ncol(df)
if (num_cols < 3) {
return(df)
}
for (i in 3:num_cols) {
df <- df %>%
mutate(across(all_of(names(df)[i]), ~ paste0(df[[i - 2]], .)))
}
return(df)
}
result = process_columns(input)
identical(result, test)
# [1] TRUE
Solving the challenge of Generate the result table with Tn = T(n-2) & Tn with Excel VBA
Excel VBA solution 1 for Generate the result table with Tn = T(n-2) & Tn, proposed by Nicolas Micot:
VBA solution:
Function f_challenge313(Plage As Range) As Variant
Dim tableau As Variant
tableau = Plage.Value
For i = 1 To UBound(tableau, 1)
For j = 3 To UBound(tableau, 2)
tableau(i, j) = tableau(i, j - 2) & tableau(i, j)
Next j
Next i
f_challenge313 = tableau
End Function
&&