Generate the grid as per number given in cell A1. For A1=7, sample grid is given
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 260
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Generate Sequential Number Grid with Power Query
Power Query solution 1 for Generate Sequential Number Grid, proposed by Bo Rydobon 🇹🇭:
let
n = 7
in
Table.FromColumns(
List.Transform({1 .. n}, each List.Reverse({"A" .. Character.FromNumber(_ + 64)}))
)Power Query solution 2 for Generate Sequential Number Grid, proposed by Zoran Milokanović:
let
Source = {"A" .. "Z"},
n = 7
in
Table.FromRows(List.Accumulate({0 .. n - 1}, {}, (s, c) => s & {List.FirstN(List.Repeat({null}, c) & Source, n)}))
OR
let
Source = {"A" .. "Z"},
n = 7
in
Table.FromColumns(List.Accumulate({1 .. n}, {}, (s, c) => s & {List.Reverse(List.FirstN(Source, c))}))
Power Query solution 3 for Generate Sequential Number Grid, proposed by Luan Rodrigues:
let
Fonte = 7,
res = Table.FromColumns(
List.Transform({1 .. Fonte}, (x) => List.Reverse(List.Range({"A" .. "Z"}, 0, x)))
)
in
resPower Query solution 4 for Generate Sequential Number Grid, proposed by Rafael González B.:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content]{0}[N],
LA = {"A" .. "Z"},
LB =
let
a = List.Reverse(List.Numbers(1, Source)),
b = Table.FromList(a, Splitter.SplitByNothing(), {"Numbers"})
in
b,
LC = Table.AddColumn(
LB,
"Alphabet",
each
let
a = [Numbers],
b = List.FirstN(LA, a),
c = List.InsertRange(b, 0, List.Repeat({null}, Source - a)),
d = Table.FromList(c, Splitter.SplitByNothing(), {"Text"}),
e = Table.Transpose(d)
in
e
),
ans = Table.Combine(LC[Alphabet])
in
ansSolving the challenge of Generate Sequential Number Grid with Excel
Excel solution 1 for Generate Sequential Number Grid, proposed by Bo Rydobon 🇹🇭:
=MAKEARRAY(A1,A1,LAMBDA(r,c,REPT(CHAR(65+c-r),c>=r)))Excel solution 2 for Generate Sequential Number Grid, proposed by Rick Rothstein:
=LET(
b,
BASE(
10+SEQUENCE(
,
A1
)-SEQUENCE(
A1
),
36
),
IF(
ISERR(
-b
),
b,
""
)
)Excel solution 3 for Generate Sequential Number Grid, proposed by Rick Rothstein:
=IFERROR(CHAR(65+(0&(SEQUENCE(,A1)-SEQUENCE(A1)))),"")Excel solution 4 for Generate Sequential Number Grid, proposed by John V.:
=LET(s,SEQUENCE(A1),b,65+TOROW(s)-s,REPT(CHAR(b),b>64))
✅=MAKEARRAY(A1,A1,LAMBDA(r,c,REPT(CHAR(65+c-r),c>=r)))Excel solution 5 for Generate Sequential Number Grid, proposed by محمد حلمي:
=LET(i,SEQUENCE(A1),v,TOROW(i),IF(i>v,"",CHAR(v+65-i)))Excel solution 6 for Generate Sequential Number Grid, proposed by Julian Poeltl:
=LET(
M,
MAKEARRAY(
A1,
A1,
LAMBDA(
A,
B,
B+1-A
)
),
IFERROR(
CHAR(
IF(
M>0,
M+64,
""
)
),
""
)
)Excel solution 7 for Generate Sequential Number Grid, proposed by Timothée BLIOT:
=DROP(
REDUCE(
"",
SEQUENCE(
A1,
,
A1,
-1
),
LAMBDA(
a,
v,
VSTACK(
a,
HSTACK(
IFERROR(
TEXTSPLIT(
REPT(
":",
A1-v
),
":"
),
""
),
CHAR(
SEQUENCE(
,
v,
65
)
)
)
)
)
),
1,
1
)
second idea:
=MAKEARRAY(
A1,
A1,
LAMBDA(
x,
y CHAR(
IF(
y>=x,
y+65-x,
9
)
)
)
)Excel solution 8 for Generate Sequential Number Grid, proposed by Hussein SATOUR:
=LET(a, SEQUENCE(A1),
b, REPT(" ", a - 1) & LEFT(CONCAT(CHAR(SEQUENCE(, A1) + 64)), A1 - a + 1),
c, BYROW(b, LAMBDA(x, TEXTJOIN(",",, MID(x, SEQUENCE(A1), 1)) & ";")),
TEXTSPLIT(CONCAT(c), ",", ";",1,,""))Excel solution 9 for Generate Sequential Number Grid, proposed by Oscar Mendez Roca Farell:
=IFERROR(CHAR((SEQUENCE(A1)<=SEQUENCE( ,A1))*SEQUENCE( , A1, 65)), "")Excel solution 10 for Generate Sequential Number Grid, proposed by Duy Tùng:
=MAKEARRAY(
A1,
A1,
LAMBDA(
x,
y,
IF(
y>x-1,
CHAR(
65+y-x
),
""
)
)
)Excel solution 11 for Generate Sequential Number Grid, proposed by Sunny Baggu:
=IFNA(
DROP(
REDUCE(
"",
SEQUENCE(A1),
LAMBDA(a, v, HSTACK(a, CHAR(SORT(SEQUENCE(v, 1, 65), , -1))))
),
,
1
),
""
)Excel solution 12 for Generate Sequential Number Grid, proposed by Sunny Baggu:
=LET(_s,SEQUENCE(A1),IFERROR(CHAR(IF(TOROW(_s)>=_s,64+_s)),""))Excel solution 13 for Generate Sequential Number Grid, proposed by LEONARD OCHEA 🇷🇴:
=MAKEARRAY(A1,A1,LAMBDA(a,b,LET(d,b-a,IF(d>=0,CHAR(d+65),""))))Excel solution 14 for Generate Sequential Number Grid, proposed by Abdallah Ally:
=REDUCE(CHAR(SEQUENCE(,A1,65)),SEQUENCE(A1,,A1,-1),LAMBDA(x,y,IF(y=A1,x,VSTACK(x,HSTACK(DROP(TEXTSPLIT(REPT("x",A1-y),"x"),,-1),CHAR(SEQUENCE(,y,65)))))))Excel solution 15 for Generate Sequential Number Grid, proposed by Anshu Bantra:
=LET(
rows_,
SEQUENCE(
A11
),
cols_,
TOROW(
rows_
),
IF(
cols_ >= rows_,
CHAR(
cols_ - rows_ + 65
),
""
)
)Excel solution 16 for Generate Sequential Number Grid, proposed by Julien Lacaze:
=MAKEARRAY(A1,A1,LAMBDA(r,c,REPT(CHAR(64+c-r+1),c>=r)))Excel solution 17 for Generate Sequential Number Grid, proposed by Giorgi Goderdzishvili:
=LET(
nmb,A1,
arr,MAKEARRAY(nmb,nmb,LAMBDA(r,c,64+c+1-r)),
chck, SEQUENCE(nmb)<=SEQUENCE(,nmb),
final, IF(chck,CHAR(arr),""),
final)Excel solution 18 for Generate Sequential Number Grid, proposed by Daniel Garzia:
=LET(n,A1,s,SEQUENCE(n,,,-1)+SEQUENCE(,n)-1,IF(s<1,"",CHAR(64+s)))Excel solution 19 for Generate Sequential Number Grid, proposed by Md Ismail Hosen:
=LAMBDA(
Number,
IFNA(
CHAR(
REDUCE(
,
SEQUENCE(
,
Number
),
LAMBDA(
a,
v,
HSTACK(
a,
SEQUENCE(
v,
,
v,
-1
)
)
)
) +
CODE(
"A"
) - 1
),
""
)
)(A1)Excel solution 20 for Generate Sequential Number Grid, proposed by Narayanan J 🇮🇳:
=MAKEARRAY(A1,A1,LAMBDA(r,c,IF(r>c,"",CHAR(65+c-r))))Excel solution 21 for Generate Sequential Number Grid, proposed by Murat Hasanoglu:
=IFERROR(IF(COLUMN()<=$A$1;LEFT(ADDRESS(1;COLUMN()+2-ROW();4));"");"")Excel solution 22 for Generate Sequential Number Grid, proposed by Mark Robson:
=CHAR(MAKEARRAY($A$1,
$A$1,
LAMBDA(r,
c,
(IF((c-r+1)>0,
(c-r+65),
32)))))Solving the challenge of Generate Sequential Number Grid with Excel VBA
Excel VBA solution 1 for Generate Sequential Number Grid, proposed by Nicolas Micot:
Function f_genererGrille(ByVal nombre As Integer) As String()
Dim tableau() As String
Dim num As Integer
ReDim tableau(1 To nombre, 1 To nombre)
For i = 1 To nombre
num = 0
For j = i To nombre
tableau(i, j) = Chr(Asc("A") + num)
num = num + 1
Next j
Next i
f_genererGrille = tableau
End Function
&&&