Generate the result table. Here, Tn = T(n-2) & T(n-1) where & is concatenation operator.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 110
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Generate Concatenation Table with Power Query
Power Query solution 1 for Generate Concatenation Table, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Rs = Table.FromRows(
List.Transform(
Table.ToRows(Source),
(a) =>
List.Generate(
() => [n = 0, c = Text.From(a{0})],
each a{[n]}? <> null,
each [n = [n] + 1, c = [c] & Text.From(a{[n] + 1})],
each [c]
)
)
)
in
RsPower Query solution 2 for Generate Concatenation Table, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Res = Table.FromRows(
List.Transform(
Table.ToRows(Source),
each List.Accumulate(_, {}, (s, l) => s & {List.Last({""} & s) & Text.From(l)})
)
)
in
ResPower Query solution 3 for Generate Concatenation Table, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
ColumnCount = List.Count(Table.ColumnNames(Source)),
Calculation = List.Transform(
Table.ToRows(Source),
(f) =>
List.Transform(
{1 .. ColumnCount},
(x) => Text.Combine(List.Transform(List.Range(f, 0, x), (z) => Text.From(z)))
)
),
Result = Table.FromRows(Calculation)
in
ResultPower Query solution 4 for Generate Concatenation Table, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
RowList = Table.AddColumn(
Source,
"Custom",
each Text.Combine(List.Transform(Record.ToList(_), each Text.From(_)), "")
)[[Custom]],
Accumulate = List.Accumulate(
{1 .. Table.ColumnCount(Source)},
RowList,
(s, c) => Table.AddColumn(s, "Col" & Text.From(c), each Text.Start([Custom], c))
),
Solucion = Table.RemoveColumns(Accumulate, {"Custom"})
in
SolucionPower Query solution 5 for Generate Concatenation Table, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
lst = Table.AddColumn(Fonte, "Personalizar", each {1 .. List.Count(Record.FieldValues(_))}),
exp = Table.ExpandListColumn(lst, "Personalizar"),
con = Table.AddColumn(
exp,
"Personalizar.1",
each Text.Combine(List.FirstN(List.Transform(Record.FieldValues(_), Text.From), [Personalizar]))
),
grp = Table.Group(con, {"Coluna1"}, {{"Contagem", each Text.Combine([Personalizar.1], "|")}})[
[Contagem]
],
res = Table.SplitColumn(
grp,
"Contagem",
Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv),
List.Count(Table.ColumnNames(Fonte))
)
in
resPower Query solution 6 for Generate Concatenation Table, proposed by Bhavya Gupta:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Fn = (l as list) =>
List.Generate(
() => [x = 1, y = l{0}],
each [x] <= List.Count(l),
each [x = [x] + 1, y = [y] & l{[x]}],
each [y]
),
EO = Table.FromRows(List.Transform(Table.ToRows(Source), each Fn(List.Transform(_, Text.From))))
in
EOPower Query solution 7 for Generate Concatenation Table, proposed by Bhavya Gupta:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Final = Table.FromRows(
Table.TransformRows(
Source,
each List.Skip(
List.Accumulate(Record.ToList(_), {""}, (s, c) => s & {List.Last(s) & Text.From(c)})
)
)
)
in
FinalPower Query solution 8 for Generate Concatenation Table, proposed by Matthias Friedmann:
let
Source = Excel.CurrentWorkbook(){[Name = "Concatenate"]}[Content],
Transform = Table.FromRows(
Table.TransformRows(
Source,
each
let
a = List.Transform(Record.ToList(_), Text.From)
in
List.Generate(
() => [i = 0, x = a{0}],
each [i] <= Table.ColumnCount(Source) - 1,
each [i = [i] + 1, x = [x] & a{i}],
each [x]
)
)
)
in
TransformPower Query solution 9 for Generate Concatenation Table, proposed by Matthias Friedmann:
let
Source = Excel.CurrentWorkbook(){[Name = "Concatenate"]}[Content],
Custom1 = Table.FromList(
Table.TransformRows(
Source,
each Text.Combine(
List.Transform(
{1 .. Table.ColumnCount(Source)},
(x) => Text.Combine(List.FirstN(List.Transform(Record.ToList(_), Text.From), x))
),
", "
)
)
)
in
Custom1Power Query solution 10 for Generate Concatenation Table, proposed by Victor Wang:
let
Source = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
Custom1 = Table.FromRecords(
Table.TransformRows(
Source,
each
let
l = List.Transform(Record.FieldValues(_), Text.From)
in
Record.FromList(
List.Transform({1 .. List.Count(l)}, each Text.Combine(List.FirstN(l, _))),
Table.ColumnNames(Source)
)
)
)
in
Custom1Power Query solution 11 for Generate Concatenation Table, proposed by Krzysztof Kominiak:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ChanType = List.Accumulate(
Table.ColumnNames(Source),
Source,
(S, C) => Table.TransformColumnTypes(S, {{C, type text}})
),
Base = Table.AddColumn(ChanType, "Temp", each Record.ToList(_))[[Temp]],
AccumList = Table.RemoveColumns(
List.Accumulate(
{1 .. Table.ColumnCount(ChanType)},
Base,
(S, C) => Table.AddColumn(S, "C" & Text.From(C), each Text.Combine(List.Range([Temp], 0, C)))
),
"Temp"
)
in
AccumListPower Query solution 12 for Generate Concatenation Table, proposed by Jan Willem Van Holst:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"LctJCoAwFATRu/Q6G+fDhL9wiLOCIoie3iK4eFDQtPeq5dSgRYeAHoPMeY3UhBkLVmzY43pQ9/85ceHBG9eESpEhR4ESlcw+",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [
Column1 = _t,
Column2 = _t,
Column3 = _t,
Column4 = _t,
Column5 = _t,
Column6 = _t,
Column7 = _t
]
),
#"Added Custom" = Table.AddColumn(
Source,
"Result",
(x) =>
let
myList = Record.ToList(x),
listGen = List.Generate(
() => [counter = 0, cum = myList{0}],
each [counter] < List.Count(myList),
each [counter = [counter] + 1, cum = [cum] & myList{counter}],
each [cum]
),
lastElement = List.Last(listGen)
in
lastElement
)
in
#"Added Custom"Solving the challenge of Generate Concatenation Table with Excel
Excel solution 1 for Generate Concatenation Table, proposed by Bo Rydobon 🇹🇭:
=LET(
z,
A2:G5,
MID(
SCAN(
,
z,
LAMBDA(
a,
v,
a&v
)
),
SEQUENCE(
ROWS(
z
),
,
,
COLUMNS(
z
)
),
99
)
)
=LEFT(
BYROW(
A2:G5,
LAMBDA(
r,
REDUCE(
,
r,
LAMBDA(
a,
v,
a&v
)
)
)
),
SEQUENCE(
,
COLUMNS(
A2:G5
)
)
)
=LET(
z,
A2:G5,
MAP(
z,
IFNA(
SEQUENCE(
ROWS(
z
)
),
z
),
LAMBDA(
a,
r,
CONCAT(
INDEX(
A2:A5,
r
):a
)
)
)
)Excel solution 2 for Generate Concatenation Table, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:G5,
LAMBDA(
a,
CONCAT(
TAKE(
A2:a,
-1
)
)
)
)Excel solution 3 for Generate Concatenation Table, proposed by Rick Rothstein:
=SCAN(
"",
A2:G2,
LAMBDA(
a,
x,
a&x
)
)Excel solution 4 for Generate Concatenation Table, proposed by Rick Rothstein:
=HSTACK(
MID(
BYROW(
A2:G5,
LAMBDA(
r,
CONCAT(
r
)
)
),
1,
SEQUENCE(
,
COLUMNS(
A2:G5
)
)
)
)Excel solution 5 for Generate Concatenation Table, proposed by John V.:
=SCAN(
"",
A2:G5,
LAMBDA(
i,
x,
REPT(
i,
COLUMNS(
A2:x
)>1
)&x
)
)
✅=MID(
CONCAT(
A2:G5
),
{1;2;3;4},
SEQUENCE(
,
7
)
)Excel solution 6 for Generate Concatenation Table, proposed by محمد حلمي:
=SCAN(
,
A2:G2,
LAMBDA(
a,
d,
a&d
)
)Excel solution 7 for Generate Concatenation Table, proposed by محمد حلمي:
=TEXTSPLIT(
TEXTJOIN(
"/",
,
BYROW(
A2:G5,
LAMBDA(
a,
CONCAT(
SCAN(
,
a,
LAMBDA(
a,
d,
a&d
)
)&"-"
)
)
)
),
"-",
"/",
1
)Excel solution 8 for Generate Concatenation Table, proposed by محمد حلمي:
=REDUCE(
,
A2:G2,
LAMBDA(
a,
d,
HSTACK(
a,
TAKE(
a,
,
-1
)&d
)
)
)Excel solution 9 for Generate Concatenation Table, proposed by محمد حلمي:
=MID(CONCAT(A2:G5),SEQUENCE(4,,,7),SEQUENCE(,7))Excel solution 10 for Generate Concatenation Table, proposed by محمد حلمي:
=DROP(
REDUCE(
"",
SEQUENCE(
ROWS(
A2:G5
)
),
LAMBDA(
x,
y,
VSTACK(
x,
SCAN(
,
INDEX(
A2:G5,
y,
),
LAMBDA(
a,
d,
a&d
)
)
)
)
),
1
)Excel solution 11 for Generate Concatenation Table, proposed by 🇰🇷 Taeyong Shin:
=SCAN(
,
A2:G5,
LAMBDA(
a,
b,
REPT(
a,
COLUMNS(
A2:b
)>1
) & b
)
)Excel solution 12 for Generate Concatenation Table, proposed by 🇰🇷 Taeyong Shin:
= LAMBDA(
range,
[n],
LET(
nth,
IF(
ISOMITTED(
n
),
1,
n
),
CONC,
LAMBDA(
Recd,
LET(
Loop,
LAMBDA(
ME,
range,
n,
LET(
String,
CONCAT(
range
),
txt,
LEFT(
String,
n
),
IF(
n = LEN(
String
),
txt,
HSTACK(
txt,
ME(
ME,
range,
n + 1
)
)
)
)
),
Loop(
Loop,
Recd,
1
)
)
),
Str,
CONC(
INDEX(
range,
nth,
)
),
IF(
nth = ROWS(
ran&ge
),
Str,
VSTACK(
Str,
ROWCONCAT(
range,
nth + 1
)
)
)
)
);
=ROWCONCAT(
A2:G5
)
*************************************************************************
=LET(
Func,
LAMBDA(
n,
SCAN(
,
INDEX(
A2:G5,
n,
),
LAMBDA(
a,
b,
a & b
)
)
),
REDUCE(
Func(
1
),
SEQUENCE(
ROWS(
A2:G5
) - 1
) + 1,
LAMBDA(
a,
n,
VSTACK(
a,
Func(
n
)
)
)
)
)
=MAKEARRAY(
ROWS(
A2:G5
),
COLUMNS(
A2:G5
),
LAMBDA(
r,
c,
CONCAT(
INDEX(
A2:G5,
r,
1
):INDEX(
A2:G5,
r,
c
)
)
)
)Excel solution 13 for Generate Concatenation Table, proposed by Kris Jaganah:
=LET(
a,
BYROW(
A2:G5,
LAMBDA(
p,
CONCAT(
SCAN(
,
CHAR(
SEQUENCE(
,
COLUMNS(
A2:G2
),
CODE(
p
),
1
)
),
LAMBDA(
x,
y,
x&y
)
)
)
)
),
b,
1+SEQUENCE(
7,
,
0
)+VSTACK(
0,
SCAN(
,
SEQUENCE(
6,
,
0
),
LAMBDA(
x,
y,
x+y
)
)
),
c,
MID(
a,
TRANSPOSE(
b
),
SEQUENCE(
,
7
)
),
c
)Excel solution 14 for Generate Concatenation Table, proposed by Julian Poeltl:
=TEXTSPLIT(
TEXTJOIN(
"|",
,
BYROW(
A2:G5,
LAMBDA(
A,
TEXTJOIN(
",",
,
SCAN(
,
A,
LAMBDA(
A,
B,
A&B
)
)
)
)
)
),
",",
"|"
)Excel solution 15 for Generate Concatenation Table, proposed by Aditya Kumar Darak 🇮🇳:
=REDUCE(
A2:A5,
SEQUENCE(
COLUMNS(
A2:G5
) - 1,
,
2
),
LAMBDA(
a,
b,
HSTACK(
a,
TAKE(
a,
,
-1
) & INDEX(
A2:G5,
,
b
)
)
)
)Excel solution 16 for Generate Concatenation Table, proposed by Aditya Kumar Darak 🇮🇳:
=MAKEARRAY(
ROWS(
A2:G5
),
COLUMNS(
A2:G5
),
LAMBDA(
r,
c,
CONCAT(
TAKE(
INDEX(
A2:G5,
r,
0
),
,
c
)
)
)
)Excel solution 17 for Generate Concatenation Table, proposed by Timothée BLIOT:
=LET(
A,
A2:G5,
MAKEARRAY(
ROWS(
A
),
COLUMNS(
A
),
LAMBDA(
x,
y,
CONCAT(
INDEX(
A,
x,
SEQUENCE(
y
)
)
)
)
)
)
Another option:
=TEXTSPLIT(
TEXTJOIN(
"/",
1,
BYROW(
A2:G5,
LAMBDA(
x,
TEXTJOIN(
",",
1,
SCAN(
,
x,
LAMBDA(
a,
v,
a&v
)
)
)
)
)
),
",",
"/"
)Excel solution 18 for Generate Concatenation Table, proposed by Duy Tùng:
=LEFT(BYROW(A2:G5,CONCAT),COLUMN(A2:G2))Excel solution 19 for Generate Concatenation Table, proposed by Bhavya Gupta:
=LET(
rng,
A3:G6,
a,
BYROW(
rng,
LAMBDA(
x,
CONCAT(
x
)
)
),
c,
COLUMNS(
rng
),
b,
SEQUENCE(
,
c
),
MAP(
IFNA(
a,
b
),
IFNA(
b,
a
),
LAMBDA(
q,
w,
REPLACE(
q,
w+1,
c-w,
""
)
)
)
)Excel solution 20 for Generate Concatenation Table, proposed by Bhavya Gupta:
=MAKEARRAY(
ROWS(
A2:G5
),
COLUMNS(
A2:G5
),
LAMBDA(
r,
c,
CONCAT(
TAKE(
CHOOSEROWS(
A2:G5,
r
),
,
c
)
)
)
)Excel solution 21 for Generate Concatenation Table, proposed by Md. Zohurul Islam:
=LET(
z,
A2:G5,
A,
BYROW(
z,
LAMBDA(
z,
TEXTJOIN(
"/",
,
SCAN(
"",
z,
LAMBDA(
x,
y,
x&y
)
)
)
)
),
B,
DROP(
REDUCE(
"",
A,
LAMBDA(
p,
q,
VSTACK(
p,
TEXTSPLIT(
q,
"/"
)
)
)
),
1
),
B
)Excel solution 22 for Generate Concatenation Table, proposed by Charles Roldan:
=LET(
Mat,
A2:G5,
MAKEARRAY(
ROWS(
Mat
),
COLUMNS(
Mat
),
LAMBDA(
r,
c,
CONCAT(
TAKE(
CHOOSEROWS(
Mat,
r
),
,
c
)
)
)
)
)
=TEXTSPLIT(
TEXTJOIN(
";" ,
,
BYROW(
A2:G5,
LAMBDA(
x,
TEXTJOIN(
"," ,
,
SCAN(
,
x,
LAMBDA(
a,
b,
a&b
)
)
)
)
)
),
"," ,
";"
)Excel solution 23 for Generate Concatenation Table, proposed by Jaroslaw Kujawa:
=LET(
a,
data,
b,
LEFT(
BYROW(
a,
LAMBDA(
d,
CONCAT(
d
)
)
),
SEQUENCE(
,
COLUMNS(
a
)
)
),
b
)
With text/non-text:
=LET(
a,
data,
b,
IF(
NOT(
ISNUMBER(
TAKE(
a,
,
1
)
)
),
LEFT(
BYROW(
a,
LAMBDA(
c,
CONCAT(
c
)
)
),
SEQUENCE(
,
COLUMNS(
a
)
)
),
--LEFT(
BYROW(
a,
LAMBDA(
c,
CONCAT(
c
)
)
),
SEQUENCE(
,
COLUMNS(
a
)
)
)
),
b
)Excel solution 24 for Generate Concatenation Table, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
={MID(
CONCAT(
$A2:$G2
),
1,
COLUMN(
A1:G1
)
)Excel solution 25 for Generate Concatenation Table, proposed by Stefan Olsson:
=BYROW(
A2:G5,
LAMBDA(
rr,
SCAN(
"",
rr,
LAMBDA(
a,
b,
a&b
)
)
)
)Excel solution 26 for Generate Concatenation Table, proposed by Victor Momoh (MVP, MOS, R.Eng):
=LET(
p,
A2:G5,
DROP(
REDUCE(
"",
ROW(
p
),
LAMBDA(
x,
y,
VSTACK(
x,
SCAN(
"",
INDEX(
p,
y-1,
0
),
LAMBDA(
a,
b,
a&b
)
)
)
)
),
1
)
)Excel solution 27 for Generate Concatenation Table, proposed by Abhishek Kumar Jain:
=P2&A2 (Put this in Q2 and drag all the way right and down till the array limits the old fashioned way)
Option 2:
=TEXTSPLIT(
TEXTJOIN(
" ",
FALSE,
BYROW(
A2:G5,
LAMBDA(
a,
TEXTJOIN(
"|",
FALSE,
SCAN(
"",
a,
LAMBDA(
x,
y,
x&y
)
)
)
)
)
),
"|",
" "
)Excel solution 28 for Generate Concatenation Table, proposed by Guillermo Arroyo:
=SCAN(
"",
A2:G5,
LAMBDA(
a,
b,
IF(
COLUMNS(
A2:b
)=1,
b,
a&b
)
)
)Excel solution 29 for Generate Concatenation Table, proposed by roberto mensa:
=MID(TEXTJOIN(
,
,
rng
),
MMULT(--(ROW(
OFFSET(
rng,
,
1
)
)>TRANSPOSE(
ROW(
OFFSET(
rng,
,
1
)
)
)),
MMULT(
LEN(
rng
),
TRANSPOSE(
COLUMN(
rng
)^0
)
))+1,
TRANSPOSE(MMULT(--(COLUMN(
rng
)<=TRANSPOSE(
COLUMN(
rng
)
)),
TRANSPOSE(
LEN(
rng
)
))))Excel solution 30 for Generate Concatenation Table, proposed by Enrico Giorgi:
=TEXTJOIN(
"",
TRUE,
INDEX(
$A$2:$A$5,
ROW(
1:4
),
1
):INDEX(
$A$2:$G$7,
ROW(
1:4
),
COLUMN(
1:7
)
)
)
ITALIAN VERSION
=TESTO.UNISCI(
"";VERO;INDICE(
$A$2:$A$5;RIF.RIGA(
1:4
);1
):INDICE(
$A$2:$G$7;RIF.RIGA(
1:4
);RIF.COLONNA(
1:7
)
)
)Excel solution 31 for Generate Concatenation Table, proposed by Tushar Mehta:
=LET(
rslt,
SCAN(
,
A2:G5,
LAMBDA(
a,
c,
a&c
)
),
RIGHT(
rslt,
MOD(
LEN(
rslt
)-1,
7
)+1
)
)Excel solution 32 for Generate Concatenation Table, proposed by Aniket T.:
=mid(concatenate ($A2,
$B2,
$C2,
$D2,
$E2,
$F2,
$G2),
1,
Counta(
$A2:A2
))Solving the challenge of Generate Concatenation Table with Excel VBA
Excel VBA solution 1 for Generate Concatenation Table, proposed by Md. Zohurul Islam:
Sub ExcelCHallenge110()
Dim nr As Long, r As Long
Dim nc As Long, c As Long
Dim a, b, d, result
Dim rng As Range
'headers
Range("I1") = "VBA Solution"
Set rng = Range("A2:G5")
'step2
nr = rng.Rows.Count
nc = rng.Columns.Count
For r = 1 To nr
Range("I" & r + 1) = Cells(r + 1, 1).Value
For c = 1 To nc
a = Cells(r + 1, c + 0).Value
b = Cells(r + 1, c + 8).Offset(0, -1).Value
result = b & a
'post
Cells(r + 1, c + 8).NumberFormat = "@"
Cells(r + 1, c + 8) = result
Next c
Next r
End Sub
Solving the challenge of Generate Conc&atenation Table with SQL
SQL solution 1 for Generate Concatenation Table, proposed by Zoran Milokanović:
WITH /* Microsoft SQL Server 2019 */
DATA_PREP
AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ORDINAL_NUMBER
,P.A, P.B, P.C, P.D, P.E, P.F, P.G
FROM PROBLEM P
),
TRANSPOSE
AS
(
SELECT
P.COL
,P.[1], P.[2], P.[3], P.[4]
FROM
(
SELECT
U.ORDINAL_NUMBER, U.COL, U.VALUE
FROM DATA_PREP DP
UNPIVOT
(
VALUE FOR COL IN (A, B, C, D, E, F, G)
) U
) T
PIVOT
(
MAX(T.VALUE)
FOR T.ORDINAL_NUMBER IN ([1], [2], [3], [4])
) P
),
CALCULATION
AS
(
SELECT
T2.COL
,STRING_AGG(T1.[1], '') AS [1]
,STRING_AGG(T1.[2], '') AS [2]
,STRING_AGG(T1.[3], '') AS [3]
,STRING_AGG(T1.[4], '') AS [4]
FROM TRANSPOSE T1
JOIN TRANSPOSE T2 ON T1.COL <= T2.COL
GROUP BY
T2.COL
)
SELECT
P.A AS I, P.B AS J, P.C AS K, P.D AS L, P.E AS M, P.F AS N, P.G AS O
FROM
(
SELECT
U.ORDINAL_NUMBER, U.COL, U.VALUE
FROM CALCULATION C
UNPIVOT
(
VALUE FOR ORDINAL_NUMBER IN ([1], [2], [3], [4])
) U
) T
PIVOT
(
MAX(T.VALUE)
FOR T.COL IN ([A], [B], [C], [D], [E], [F], [G])
) P
;
&