Make the staircase of alphabets. The number of stairs are given in cell B2. Example has been given for 26. If B2 > 26, then take modulus of B2 to make 27 equal to 1, 28 to 2 and so on.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 523
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Build Alphabet Staircase with Power Query
Power Query solution 1 for Build Alphabet Staircase, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = 28,
Num = {0 .. 25},
Sol = Table.Combine(
List.Transform(
List.Transform(
{0 .. Source - 1},
each List.Repeat({null, null}, _)
& List.Repeat({Character.FromNumber(Num{Number.Mod(_, 26)} + 65)}, 3)
),
each Table.FromRows({_})
)
)
in
SolPower Query solution 2 for Build Alphabet Staircase, proposed by Luan Rodrigues:
let
n = 26,
vl = if n > 26 then Number.Mod(n, 26) else n,
lt = List.Transform({"A" .. "Z"}, each List.Repeat({_}, 3)),
lst = List.RemoveNulls(
List.Transform(
List.Zip({List.Transform(List.Numbers(0, vl, 2), each List.Repeat({null}, _)), lt}),
each try Table.FromRows({List.Combine(_)}) otherwise null
)
),
res = Table.Combine(lst)
in
resPower Query solution 3 for Build Alphabet Staircase, proposed by Mihai Radu O:
let
_n = 26,
lt = List.Transform,
lc = List.Contains,
row = if _n > 26 then Number.Mod(_n, 26) else _n,
col = 2 * row + 1,
a = {1 .. row},
b = lt(
a,
(r) =>
[
_a = {Number.Power(r, 0) .. col},
_b = lt(
_a,
(c) =>
if lc(lt({- 1, 0, 1}, (x) => x + 2 * r), c) then Character.FromNumber(r + 64) else null
)
][_b]
),
c = Table.FromRows(b)
in
cSolving the challenge of Build Alphabet Staircase with Excel
Excel solution 1 for Build Alphabet Staircase, proposed by Bo Rydobon 🇹🇭:
=LET(
n,
B2,
MAKEARRAY(
n,
n*2+1,
LAMBDA(
r,
c,
REPT(
CHAR(
MOD(
r-1,
26
)+65
),
ABS(
c-r*2
)<2
)
)
)
)
=LET(
n,
B2,
MAKEARRAY(
n,
MIN(
n*2+1,
53
),
LAMBDA(
r,
c,
LET(
a,
MOD(
r-1,
26
)+1,
REPT(
CHAR(
a+64
),
ABS(
c-a*2
)<2
)
)
)
)
)Excel solution 2 for Build Alphabet Staircase, proposed by Bo Rydobon 🇹🇭:
=LET(
n,
MOD(
B2-1,
26
)+1,
MAKEARRAY(
n,
n*2+1,
LAMBDA(
r,
c,
REPT(
CHAR(
r+64
),
ABS(
c-r*2
)<2
)
)
)
)Excel solution 3 for Build Alphabet Staircase, proposed by Rick Rothstein:
=MAKEARRAY(B2,
2*B2+2,
LAMBDA(r,
c,
IF((c>2*r-2)*(c<2*r+2),
CHAR(
MOD(
r-1,
26
)+65
),
"")))Excel solution 4 for Build Alphabet Staircase, proposed by Rick Rothstein:
=MAKEARRAY(
B2,
2*B2+1,
LAMBDA(
r,
c,
IF(
ABS(
2*r-c
)<2,
CHAR(
MOD(
r-1,
26
)+65
),
""
)
)
)Excel solution 5 for Build Alphabet Staircase, proposed by John V.:
=LET(i,1+MOD(B2-1,26),s,SEQUENCE,REPT(CHAR(64+s(i)),ABS(2*s(i)-s(,1+2*i))<2))Excel solution 6 for Build Alphabet Staircase, proposed by محمد حلمي:
=IFNA(DROP(REDUCE(0,SEQUENCE(B2),LAMBDA(a,v,
VSTACK(a,IFS(SEQUENCE(,v*2+1)>=(v*2-1),
CHAR(MOD(v-1,26)+65))))),1),"")Excel solution 7 for Build Alphabet Staircase, proposed by محمد حلمي:
=MAKEARRAY(
B2,
B2*2+1,
LAMBDA(
r,
c,
REPT(
CHAR(
MOD(
r-1,
26
)+65
),
OR(
r*2+{-1,
0,
1}=c
)
)
)
)Excel solution 8 for Build Alphabet Staircase, proposed by Kris Jaganah:
=MAKEARRAY(B2,B2*2+1,LAMBDA(x,y,IF((y-2*x+1>=0)*(y-2*x+1<3),CHAR(x+64),"")))Excel solution 9 for Build Alphabet Staircase, proposed by Julian Poeltl:
=LET(R,
B2,
MAKEARRAY(R,
R*2+1,
LAMBDA(A,
B,
IF((B<=3+(A-1)*2)*(B>A*2-2),
CHAR(
MOD(
A-1,
26
)+65
),
""))))Excel solution 10 for Build Alphabet Staircase, proposed by Aditya Kumar Darak 🇮🇳:
=MAKEARRAY(
B2,
B2 * 2 + 1,
LAMBDA(
r,
c,
LET(
q,
QUOTIENT(
c + r - 2,
3
),
r,
IF(
q = r - 1,
CHAR(
65 + MOD(
q,
26
)
),
""
),
r
)
)
)Excel solution 11 for Build Alphabet Staircase, proposed by Timothée BLIOT:
=IFNA(DROP(REDUCE("",SEQUENCE(B2),LAMBDA(w,v,VSTACK(w,TEXTSPLIT(REPT("|",2*v)&REPT(CHAR(MOD(v-1,26)+65)&"|",3),"|",,0)) )),1,2),"")Excel solution 12 for Build Alphabet Staircase, proposed by Oscar Mendez Roca Farell:
=LET(n, MOD(B2-1, 26)+1, MAKEARRAY(n, 2*n+1, LAMBDA(r, c, IF((r+1>c/2)*(r-1Excel solution 13 for Build Alphabet Staircase, proposed by Sunny Baggu:
=LET(
v,
B2,
b,
MOD(
v,
26
),
n,
IF(
v <= 26,
v,
IF(
b = 0,
26,
b
)
),
_s,
SEQUENCE(
n
),
_r,
SEQUENCE(
,
n * 2 + 1
),
_Sp,
2 * _s - 1,
_Ep,
2 * _s + 1,
_v,
(_r >= _Sp) * (_r <= _Ep) * (_s + 64),
IF(
_v,
CHAR(
_v
),
""
)
)Excel solution 14 for Build Alphabet Staircase, proposed by Abdallah Ally:
=LET(n,
26,
DROP(DROP(REDUCE("",
SEQUENCE(
n
),
LAMBDA(x,
y,
LET(a,
IF(y=1,
"",
EXPAND("",
,
(y-1)*2,
"")),
b,
EXPAND(
CHAR(
y+64
),
,
3,
CHAR(
y+64
)
),
c,
IF(y=n,
"",
EXPAND("",
,
2*(n-y),
"")),
VSTACK(
x,
HSTACK(
a,
b,
c
)
)))),
1,
1),
,
-1))Excel solution 15 for Build Alphabet Staircase, proposed by ferhat CK:
=LET(a,
TAKE(
CHAR(
SEQUENCE(
B2+1,
,
65
)
),
B2
),
b,
SEQUENCE(
B2,
,
1,
2
),
c,
MAKEARRAY(B2,
B2*2+1,
LAMBDA(x,
y,
IF((y=CHOOSEROWS(
b,
x
))+(y=CHOOSEROWS(
b,
x
)+1)+(y=CHOOSEROWS(
b,
x
)+2),
CHOOSEROWS(
a,
x
),
""))),
c)Excel solution 16 for Build Alphabet Staircase, proposed by Andy Heybruch:
=TEXTSPLIT(CONCAT(MAP(SEQUENCE(B2),LAMBDA(a,
REPT(";",(a-1)*2)&
REPT(CHAR(MOD(a-1,26)+65)&";",2)&
CHAR(MOD(a-1,26)+65)&"|"))),";","|",0,,"")Excel solution 17 for Build Alphabet Staircase, proposed by Bilal Mahmoud kh.:
=IFERROR(
DROP(
TEXTSPLIT(
TEXTJOIN(
",",
,
MAP(
SEQUENCE(
B3
),
LAMBDA(
x,
TEXTJOIN(
"-",
,
CHAR(
SEQUENCE(
,
x*2,
32,
0
)
),
CHAR(
SEQUENCE(
,
3,
x+64,
0
)
)
)
)
)
),
"-",
","
),
,
2
),
""
)Excel solution 18 for Build Alphabet Staircase, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=SUBSTITUTE(LET(c,REPT("x,",SEQUENCE(MOD(B2,26),,0,2)),b,REPT("x,",SEQUENCE(26,,0,2)),a,IF(B2/26=INT(B2/26),CHAR(ROW(INDIRECT("A65"&":"&"A"&64+26))),CHAR(ROW(INDIRECT("A65"&":"&"A"&64+MOD(B2,26))))),IF(B2/26=INT(B2/26),IFERROR(DROP(TEXTSPLIT(TEXTJOIN(,,b&REPT(a&",",3)&b&"/"),",","/"),-1),""),IFERROR(DROP(TEXTSPLIT(TEXTJOIN(,,c&REPT(a&",",3)&c&"/"),",","/"),-1),""))),"x","")Excel solution 19 for Build Alphabet Staircase, proposed by Imam Hambali:
=LET(
val, B2,
cs, SEQUENCE(val,,65),
i, IF(cs<91, CHAR(cs), MOD(SEQUENCE(val),26)),
w, WRAPROWS(TOCOL(IF(SEQUENCE(val), SEQUENCE(, val*3-val+3 ))),val*3-(val-1)),
DROP(IF((--(w=1)+--(w=2)+--(w=3))=1, i, ""),-1)
)Excel solution 20 for Build Alphabet Staircase, proposed by Eddy Wijaya:
=LET(
r,B2,
adjR,MOD(r - 1, 26) + 1,
c,CHAR(SEQUENCE(adjR,,65)),
genArr,REPT(",",SEQUENCE(adjR,,0,2))&REPT(c&",",3),
DROP(REDUCE(0,genArr,LAMBDA(a,v,IFNA(VSTACK(a,DROP(TEXTSPLIT(v,","),,-1)),""))),1))Excel solution 21 for Build Alphabet Staircase, proposed by Edwin Tisnado:
=IFERROR(DROP(REDUCE(0,
SEQUENCE(
MOD(
B2-1,
26
)+1
),
LAMBDA(x,
y,
LET(a,
CHAR((y+64)*{1,
1,
1}),
VSTACK(
x,
HSTACK(
SEQUENCE(
,
2*y
)/0,
a
)
)))),
1,
2),
"")Excel solution 22 for Build Alphabet Staircase, proposed by Ricardo Alexis Domínguez Hernández:
=MAKEARRAY(B2,2*B2+1,LAMBDA(a,b,
IF(AND(b<=2*a+1,b>=2*a-1),
CHAR(MOD(a-1,26)+65),"")))Solving the challenge of Build Alphabet Staircase with Python
Python solution 1 for Build Alphabet Staircase, proposed by Konrad Gryczan, PhD:
import pandas as pd
import numpy as np
path = "523 Alphabets Staircase.xlsx"
given_number = pd.read_excel(path, usecols="B", header=None, skiprows=1, nrows=1).iloc[0, 0]
test = pd.read_excel(path, skiprows=3, header=None)
M = [[np.NaN for _ in range(given_number * 2 + 1)] for _ in range(given_number)]
for i in range(given_number):
start_col = 2 * i
M[i][start_col:start_col + 3] = [chr(ord('A') + i)] * 3
M = pd.DataFrame(M).dropna(axis=0, how='all').dropna(axis=1, how='all')
M.columns = test.columns
print(M.equals(test)) # True
Solving the challenge of Build Alphabet Staircase with R
R solution 1 for Build Alphabet Staircase, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
library(janitor)
path = "Excel/523 Alphabets Staircase.xlsx"
given_number = read_excel(path, range = "B2", col_names = FALSE) %>% pull
test = read_excel(path, skip = 3, col_names = FALSE)
M = matrix(nrow = given_number, ncol = given_number * 2 + 1)
for (i in 1:given_number) {
start_col <- 2 * (i - 1) + 1
M[i, seq(start_col, start_col + 2)] = LETTERS[i]
}
M = as_tibble(M) %>% remove_empty(c("rows", "cols"))
colnames(M) = colnames(test)
identical(M, test)
#> [1] TRUE
Solving the challenge of Build Alphabet Staircase with Excel VBA
_x000D_Excel VBA solution 1 for Build Alphabet Staircase, proposed by Md. Zohurul Islam:
Sub ExcelBI_CH523_Solution()
Dim n As Long, x As Long
Dim Lettr As String
Dim y As Long
Dim k As Long
n = Range("B2").Value
y = 65
k = 1
For x = 1 To n
Lettr = Chr(y)
Cells(x + 3, k) = Lettr
Cells(x + 3, k + 1) = Lettr
Cells(x + 3, k + 2) = Lettr
y = y + 1
k = k + 2
Next x
End Sub