Draw the triangle of alphabets as shown. Coloring has been done to emphasize the triangle. You need not do any coloring.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 505
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Draw Alphabet Triangle with Power Query
Power Query solution 1 for Draw Alphabet Triangle, proposed by Ramiro Ayala Chávez:
let
a = {"A" .. "Z"},
b = List.Generate(
() => [i = 0, j = 1],
each [i] < List.Count(a),
each if [j] = List.Count(a) then [i = [i] + 1, j = [i] + 2] else [i = [i] + [j], j = [j] + 1],
each List.Combine(List.TransformMany(List.Range(a, [i], [j]), (x) => {null}, (x, y) => {x, y}))
),
c = List.Transform(b, each List.RemoveLastN(_, 1)),
d = List.Max(List.Transform(c, List.Count)),
e = List.Transform(
c,
each List.Repeat({null}, (d - List.Count(_)) / 2)
& _
& List.Repeat({null}, (d - List.Count(_)) / 2)
),
Sol = Table.FromRows(e)
in
SolPower Query solution 2 for Draw Alphabet Triangle, proposed by Ahmed Ariem:
let
Source = Table.FromList(
List.Transform(
List.Transform(
Splitter.SplitTextByRanges({{0, 1}, {1, 2}, {3, 3}, {6, 4}, {10, 5}, {15, 6}, {21, 5}})(
Text.Combine({"A" .. "Z"})
),
(x) => Text.ToList(x)
),
(x) =>
[
a = Text.Combine(x, ""),
b = Text.Combine(x, ";;"),
c = Text.Length(a),
d =
if c = 1 then
Text.Repeat(";", 7) & b & Text.Repeat(";", 7)
else if c = 5 then
Text.Repeat(";", 3) & b & Text.Repeat(";", 3)
else
Text.Repeat(";", 8 - c) & b & Text.Repeat(";", 7 - c + 1)
][d]
),
(x) => {x},
{"DATA"}
),
#"Split Column by Delimiter" = Table.SplitColumn(
Source,
"DATA",
Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv),
List.Transform({1 .. 15}, (x) => "DATA-" & Text.From(x))
)
in
#"Split Column by Delimiter"Power Query solution 3 for Draw Alphabet Triangle, proposed by Szabolcs Phraner:
let
ABC = {"A" .. "Z"},
//Generate ListRows by dynamically shifting the Range of ABC List + adding empty values for triangle structure
GenerateListRows = List.Generate(
() => [StartRange = 0, Range = 1, List = {"A"}],
each [StartRange] < List.Count(ABC),
each [
StartRange = [StartRange] + [Range],
Range = [Range] + 1,
List = List.Range(ABC, [StartRange] + [Range], [Range] + 1)
],
each {null} & List.Combine(List.Transform([List], each {_, null}))
),
LastRow = List.Last(GenerateListRows),
//Supplement the last ListRow with two extra null values for Triangle structure
LastRowModified = {null} & LastRow & {null},
LastRowCount = List.Count(LastRowModified),
ChangeLastRow = List.ReplaceMatchingItems(GenerateListRows, {{LastRow, LastRowModified}}),
//each ListRow needs to have the same amount of items in order to transform them into Table, I Supplement each ListRow with empty values while maintaining traingle structure
SupplementRows = List.Transform(
ChangeLastRow,
each List.Accumulate(
{1 .. LastRowCount - List.Count(_)},
_,
(s, c) =>
let
index = if Number.IsOdd(c) then 0 else List.Count(s)
in
List.InsertRange(s, index, {null})
)
),
Table = Table.FromRows(SupplementRows)
in
TablePower Query solution 4 for Draw Alphabet Triangle, proposed by Joevan Bedico:
let
Source = {"A" .. "Z"},
Anwer =
let
t = List.Transform,
h = Number.Round(Number.Sqrt(List.Count(Source) * 2)),
s = {1 .. h},
p = t(List.Skip(List.Reverse(s)), each t(s, (x) => x - _)),
r = Table.ToRows(Table.FromColumns(p & {s} & List.Reverse(p)))
in
Table.FromRows(
List.Split(
t(
List.Accumulate(
List.Combine(List.RemoveLastN(r) & {t(List.Last(r), each if _ = 1 then 0 else _)}),
{},
(s, c) => s & {if c < 0 or Number.IsEven(c) then null else List.NonNullCount(s & {c})}
),
each try Source{_ - 1} otherwise null
),
h * 2 - 1
)
)
in
AnwerSolving the challenge of Draw Alphabet Triangle with Excel
Excel solution 1 for Draw Alphabet Triangle, proposed by Bo Rydobon 🇹🇭:
=IFNA(
CHAR(
XMATCH(
SEQUENCE(
7,
11
),
SCAN(
4,
2+IFNA(
XMATCH(
SEQUENCE(
26
),
{11,
7,
4,
2}
),
)*2,
SUM
)
)+64
),
""
)Excel solution 2 for Draw Alphabet Triangle, proposed by John V.:
=LET(f,
ROW(
1:7
),
c,
COLUMN(
A:K
),
i,
(ABS(
c-6
)Excel solution 3 for Draw Alphabet Triangle, proposed by محمد حلمي:
=TOCOL(C3:M9)Excel solution 4 for Draw Alphabet Triangle, proposed by محمد حلمي:
=WRAPROWS(TEXTSPLIT(TEXTJOIN(REPT(0,SWITCH(ROW(1:26),1,4,11,4,2,10,4,8,7,6,2)),,0,CHAR(ROW(65:90))),0),11,"")Excel solution 5 for Draw Alphabet Triangle, proposed by Julian Poeltl:
=LET(I,
MAKEARRAY(7,
13,
LAMBDA(A,
B,
IF((A+B>7)*(A+B<8+A*2)*((ISEVEN(
A
)*ISEVEN(
B
))+(ISODD(
A
)*ISODD(
B
)))*((A<>7)+(B<>1)*(B<>13)),
1,
0))),
S,
SCAN(
,
I,
LAMBDA(
A,
B,
A+B
)
),
IF(
I,
CHAR(
S+64
),
""
))Excel solution 6 for Draw Alphabet Triangle, proposed by Timothée BLIOT:
=MAKEARRAY(7,
13,
LAMBDA(x,
y,
IF(AND(
y-x<=6,
x+y>=8,
ISODD(
x+y+7
),
x<7
),
CHAR((x-1)*x/2+(y+x)/2+61),
IF(
AND(
y>2,
y<12,
x=7,
ISODD(
y
)
),
CHAR(
85+INT(
y/2
)
),
""
))))Excel solution 7 for Draw Alphabet Triangle, proposed by Hussein SATOUR:
=XLOOKUP(SEQUENCE(
7,
13
),
LET(a,
SORT(--TEXTSPLIT(CONCAT(MAP(SEQUENCE(
7
),
LAMBDA(x,
CONCAT(((x*2)-1)*7-SEQUENCE(
,
x,
0,
2
)&"/")))),
,
"/",
1)),
FILTER(
a,
ABS(
a-85
)<>6
)),
CHAR(
SEQUENCE(
26
)+64
),
"")Excel solution 8 for Draw Alphabet Triangle, proposed by Sunny Baggu:
=LET(
n,
SEQUENCE(
7,
,
6,
10
),
_a,
UNIQUE(
TOCOL(
DROP(
REDUCE(
6,
SEQUENCE(
ROWS(
n
)
),
LAMBDA(
a,
v,
VSTACK(
a,
INDEX(
n,
v,
1
) + SEQUENCE(
,
v,
,
2
)
)
)
) - 1,
1,
-1
),
3
)
),
_b,
CHAR(
SEQUENCE(
26,
,
65
)
),
_c,
WRAPROWS(
SEQUENCE(
11 * 7
),
11
),
XLOOKUP(
_c,
_a,
_b,
""
)
)Excel solution 9 for Draw Alphabet Triangle, proposed by Abdallah Ally:
=LET(n,7,a,CHAR(SEQUENCE(,26,65)),DROP(REDUCE("",SEQUENCE(n), LAMBDA(x,y,LET(p,SEQUENCE(y,,(y^2-y+2)/2),q,FILTER(p,p<27), r,TEXTSPLIT(TEXTJOIN(" ",,CHOOSECOLS(a,q))," "),s,COUNTA(r), t,EXPAND("",,(2*n-1-s)/2,""),VSTACK(x,HSTACK(t,r,t))))),1))Excel solution 10 for Draw Alphabet Triangle, proposed by Pieter de B.:
=DROP(REDUCE("",SEQUENCE(7),LAMBDA(a,b,IFERROR(VSTACK(a,IF(7-b,HSTACK(EXPAND("",,7-b,""),TOROW(CHOOSE({1,2},CHAR(SEQUENCE(b,,65+SUM(SEQUENCE(b))-b)),""))),HSTACK({"",""},TOROW(CHOOSE({1,0},CHAR(SEQUENCE(5,,86))))))),""))),1)Excel solution 11 for Draw Alphabet Triangle, proposed by Bilal Mahmoud kh.:
=IFERROR(TEXTSPLIT(TEXTJOIN("|",,MAP(SEQUENCE(6),REDUCE(1,SEQUENCE(5),LAMBDA(n,m,VSTACK(n,MAX(n)+m))),LAMBDA(x,y,TEXTJOIN(",",FALSE,CHAR(SEQUENCE(ABS(x-7),,32,0)),CHAR(SEQUENCE(x,,y+64))&","&CHAR(32))))," ,"&TEXTJOIN(",",FALSE," ,"&CHAR(SEQUENCE(,5,86)))&", , "),",","|"),"")Excel solution 12 for Draw Alphabet Triangle, proposed by JvdV -:
=TEXTSPLIT(REGEXREPLACE("122A221022BC22021DEF2102GHIJ201KLMNO10PQRSTU01VWXYZ1","DK(?!d)|2",11),1,0)Solving the challenge of Draw Alphabet Triangle with Python
Python solution 1 for Draw Alphabet Triangle, proposed by Konrad Gryczan, PhD:
import pandas as pd
import string
def create_triangular_dataframe(letters):
def triangular_number(n):
return (n * (n + 1)) // 2
n = max([i for i in range(1, 100) if triangular_number(i) <= len(letters)])
num_rows = n + 1
num_cols = n * 2 + 1
df = pd.DataFrame("", index=range(num_rows), columns=range(num_cols))
letters_idx = 0
def fill_row(row_num, num_letters):
nonlocal letters_idx
start_col = (num_cols - (2 * num_letters - 1)) // 2
for j in range(num_letters):
df.iat[row_num, start_col + j * 2] = letters[letters_idx]
letters_idx += 1
for i in range(1, n + 1):
fill_row(i - 1, i)
fill_row(num_rows - 1, len(letters) - triangular_number(n))
print(df)
# Example usage
create_triangular_dataframe(list(string.ascii_uppercase))
Python solution 2 for Draw Alphabet Triangle, proposed by Anshu Bantra:
import string
import re
chars_ = string.ascii_uppercase
start_ = 0
line_ = ''
lst_ = []
for _ in range(8):
line_ = f"{(' '.join(chars_[start_:start_+_])):^11}"
if line_.split():
lst_.append(re.split('', line_))
start_ += _
lst_
Solving the challenge of Draw Alphabet Triangle with Python in Excel
Python in Excel solution 1 for Draw Alphabet Triangle, proposed by Abdallah Ally:
from string import ascii_uppercase
# Create a function to generate a triangle
def gen_alphabets_triangle(n=26):
chars = ascii_uppercase[: n]
maximum = [i for i in range(n) if (i ** 2 - i + 2) // 2 < n][-1]
values = []
for i in range(1, maximum + 1):
start = (i ** 2 - i + 2) // 2 -1
end = start + i
a = ' '.join([char for char in chars[start : end]]).split(' ')
b = [''] * ((maximum * 2 - len(a) - 1) // 2)
values.append(b + a + b)
return values
# Perfom data munging
lst = gen_alphabets_triangle()
lst
Solving the challenge of Draw Alphabet Triangle with R
R solution 1 for Draw Alphabet Triangle, proposed by Konrad Gryczan, PhD:
library(dplyr)
library(purrr)
create_triangular_dataframe <- function(letters) {
n <- max(which((1:10 * (1:10 + 1)) / 2 <= length(letters)))
num_rows <- n + 1
num_cols <- n * 2 + 1
df <- data.frame(matrix("", nrow = num_rows, ncol = num_cols))
letters_idx <- 1
fill_row <- function(row_num, num_letters) {
start_col <- (num_cols - (2 * num_letters - 1)) %/% 2 + 1
df[row_num, seq(start_col, by = 2, length.out = num_letters)] <<- letters[letters_idx:(letters_idx + num_letters - 1)]
letters_idx <<- letters_idx + num_letters
}
walk(1:n, ~ fill_row(.x, .x))
fill_row(num_rows, length(letters) - (n * (n + 1)) / 2)
colnames(df) <- LETTERS[1:ncol(df)]
print(df, row.names = FALSE)
}
df = create_triangular_dataframe(LETTERS)
R solution 2 for Draw Alphabet Triangle, proposed by Anil Kumar Goyal:
n <- 7
split(
LETTERS,
rep(1:n, 1:n)
) %>%
imap(~ {if(length(.x) == as.integer(.y)) .x else c("", .x, "")}) %>%
map(~ setNames(seq_along(.x), .x)) %>%
map(~ .x - mean(.x)) %>%
imap_dfr(~enframe(.x, name = "char", value = "col") %>%
mutate(row = as.numeric(.y))) %>%
ggplot(aes(x = col, y = row, label = char)) +
geom_tile(fill = "orange") +
geom_text(color = "white", fontface = "bold", size = 7) +
scale_y_reverse() +
theme_void()
Solving the challenge of Draw Alphabet Triangle with Excel VBA
Excel VBA solution 1 for Draw Alphabet Triangle, proposed by Ümit Barış Köse, MSc:
Sub c505()
Dim Col As Integer, Count As Integer, c As Integer, Row As Integer, i1 As Integer
Col = 8
Count = 0
c = 65
For Row = 3 To 9
For i1 = Col To (Col + Count) Step 2
Cells(Row, i1).Value = Chr(c)
c = c + 1
Next i1
If Row < 8 Then
Col = Col - 1
Count = Count + 2
Else
Col = Col + 1
Count = Count - 2
End If
Next Row
End Sub
&&