Create an alphabetic searchlight as shown.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 430
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Create Alphabetic Searchlight Grid with Power Query
Power Query solution 1 for Create Alphabetic Searchlight Grid, proposed by Bo Rydobon 🇹🇭:
let
Source = Table.FromRows(
List.Transform(
{1 .. 26},
(r) =>
List.Repeat({null}, 27 - r)
& List.Reverse(List.Transform({1 .. 27 - r}, each Character.FromNumber(_ + 64)))
& List.Repeat({null}, r * 2 - 2)
)
)
in
SourcePower Query solution 2 for Create Alphabetic Searchlight Grid, proposed by Aditya Kumar Darak 🇮🇳:
let
List = {"A" .. "Z"},
Generate = List.Transform(
{1 .. 26},
each [
L = List.Reverse(List.FirstN(List, 26 - _ + 1)),
B = List.Repeat({""}, 26 - _),
T = Table.FromRows({B & L})
][T]
),
Return = Table.Combine(Generate)
in
ReturnPower Query solution 3 for Create Alphabetic Searchlight Grid, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Sol = Table.FromColumns(
List.Zip(
List.Reverse(
List.Transform(
({0 .. 25}),
each List.Repeat({null}, _) & List.Reverse(List.FirstN({"A" .. "Z"}, _ + 1))
)
)
)
)
in
SolPower Query solution 4 for Create Alphabetic Searchlight Grid, proposed by Ramiro Ayala Chávez:
let
a = {"A" .. "Z"},
L = List.Generate(
() => [c = 0, j = a],
each [c] < List.Count(a),
each [c = [c] + 1, j = {null} & {null} & List.RemoveLastN([j])],
each [j]
),
Sol = Table.ReverseRows(Table.Transpose(Table.ReverseRows(Table.FromColumns(List.Reverse(L)))))
in
SolSolving the challenge of Create Alphabetic Searchlight Grid with Excel
Excel solution 1 for Create Alphabetic Searchlight Grid, proposed by Bo Rydobon 🇹🇭:
=LET(
s,
SEQUENCE(
26
),
c,
SEQUENCE(
,
52
),
REPT(
MID(
MID(
REPT(
0,
27-s
)&CONCAT(
CHAR(
91-s
)
),
s,
52
),
c,
1
),
c>27-s
)
)
=LET(r,
SEQUENCE(
26
),
c,
SEQUENCE(
,
52
),
REPT(CHAR(
119-r*2-c
),
(c>27-r)*(c<55-r*2)))Excel solution 2 for Create Alphabetic Searchlight Grid, proposed by Rick Rothstein:
=MAKEARRAY(
26,
51,
LAMBDA(
r,
c,
IF(
OR(
c<27-r,
c>53-2*r
),
"",
CHAR(
118-c-2*r
)
)
)
)Excel solution 3 for Create Alphabetic Searchlight Grid, proposed by Rick Rothstein:
=MAKEARRAY(26,
51,
LAMBDA(r,
c,
LEFT(CHAR(
118-c-2*r
),
(c>26-r)*(c<54-2*r))))Excel solution 4 for Create Alphabetic Searchlight Grid, proposed by John V.:
=MAKEARRAY(26,
51,
LAMBDA(r,
c,
REPT(CHAR(
118-c-2*r
),
(c+r>26)*(54-c>2*r))))Excel solution 5 for Create Alphabetic Searchlight Grid, proposed by محمد حلمي:
=DROP(
REDUCE(
0,
SEQUENCE(
26
),
LAMBDA(
a,
d,
IFNA(
VSTACK(
HSTACK(
IFERROR(
REPT(
"",
SEQUENCE(
,
d-1
)
),
""
),
CHAR(
d-SEQUENCE(
,
d
)+65
)
),
a
),
""
)
)
),
-1
)Excel solution 6 for Create Alphabetic Searchlight Grid, proposed by Julian Poeltl:
=MAKEARRAY(26,
51,
LAMBDA(A,
B,
IFERROR(CHAR(118*(B+2*A<54)*(A+B>26)-2*A-B),
"")))Excel solution 7 for Create Alphabetic Searchlight Grid, proposed by Aditya Kumar Darak 🇮🇳:
=IFERROR(
MAKEARRAY(
26,
51,
LAMBDA(
r,
c,
MID(
CONCAT(
CHAR(
SEQUENCE(
27 - r,
,
91 - r,
-1
)
)
),
r + c - 26,
1
)
)
),
""
)
Without IFERROR
=MAKEARRAY(
26,
51,
LAMBDA(
r,
c,
TRIM(
MID(
CONCAT(
REPT(
" ",
25
),
CHAR(
SEQUENCE(
27 - r,
,
91 - r,
-1
)
)
),
r + c - 1,
1
)
)
)
)Excel solution 8 for Create Alphabetic Searchlight Grid, proposed by Timothée BLIOT:
=DROP(
IFNA(
REDUCE(
"",
27-ROW(
1:26
),
LAMBDA(
a,
v,
VSTACK(
a,
HSTACK(
TEXTSPLIT(
REPT(
":",
v
),
":"
),
CHAR(
SEQUENCE(
,
v,
,
-1
)+63+v
)
)
)
)
),
""
),
1,
1
)Excel solution 9 for Create Alphabetic Searchlight Grid, proposed by Nikola Z Grujicic - Nikola Ž Grujičić:
=LET(
x,
LET(
g,
SEQUENCE(
26,
,
26,
-1
),
h,
52-2*g,
y,
MAP(
g,
h,
LAMBDA(
bg,
bh,
LET(
i,
LEFT(
REPT(
" ",
bg
)&TEXTJOIN(
"",
,
UNICHAR(
SEQUENCE(
bg,
,
64+bg,
-1
)
)
),
52-bh
)&REPT(
" ",
bh
),
i
)
)
),
VSTACK(
REPT(
" ",
52
),
y
)
),
MID(
x,
SEQUENCE(
,
52,
,
1
),
1
)
)Excel solution 10 for Create Alphabetic Searchlight Grid, proposed by Sunny Baggu:
=LET(
_a,
53 - (SEQUENCE(
,
51
) + SEQUENCE(
26
)) - SEQUENCE(
26,
,
0
) + 64,
_b,
(_a <= SEQUENCE(
26,
,
90,
-1
)) * (_a >= 65),
IF(
_b,
CHAR(
_a
),
""
)
)Excel solution 11 for Create Alphabetic Searchlight Grid, proposed by LEONARD OCHEA 🇷🇴:
=MAKEARRAY(26,
51,
LAMBDA(a,
b,
IF((b>26-a)*(b<54-2*a),
CHAR(
118-b-2*a
),
"")))Excel solution 12 for Create Alphabetic Searchlight Grid, proposed by Abdallah Ally:
=IFNA(
DROP(
LET(
a,
CHAR(
SEQUENCE(
,
26,
90,
-1
)
),
REDUCE(
"",
SEQUENCE(
26
),
LAMBDA(
x,
y,
VSTACK(
x,
HSTACK(
EXPAND(
"",
1,
27-y,
""
),
TAKE(
a,
,
y-27
)
)
)
)
)
),
1,
1
),
""
)Excel solution 13 for Create Alphabetic Searchlight Grid, proposed by Abdallah Ally:
=IFNA(
DROP(
LET(
a,
CHAR(
SEQUENCE(
,
26,
90,
-1
)
),
REDUCE(
"",
SEQUENCE(
26,
,
26,
-1
),
LAMBDA(
x,
y,
VSTACK(
x,
HSTACK(
EXPAND(
"",
,
y,
""
),
EXPAND(
TAKE(
a,
,
-y
),
,
26,
""
)
)
)
)
)
),
1,
1
),
""
)Excel solution 14 for Create Alphabetic Searchlight Grid, proposed by 🇵🇪 Ned Navarrete C.:
=MAKEARRAY(26,
51,
LAMBDA(f,
c,
IF((f+c>26)*(c+2*f<54),
CHAR(
118-2*f-c
),
"")))
=MAKEARRAY(26,
51,
LAMBDA(f,
c,
REPT(CHAR(
118-2*f-c
),
(f+c>26)*(c+2*f<54))))Excel solution 15 for Create Alphabetic Searchlight Grid, proposed by Charles Roldan:
=IFERROR(
CHAR(
CODE(
"A"
) + REDUCE(
,
SEQUENCE(
26,
,
0
),
LAMBDA(
a,
b,
VSTACK(
HSTACK(
IF(
SEQUENCE(
,
b
),
""
),
b,
TAKE(
a,
1,
-b
)
),
a
)
)
)
),
""
)Excel solution 16 for Create Alphabetic Searchlight Grid, proposed by Pieter de Bruijn:
=MAKEARRAY(26,
51,
LAMBDA(x,
y,
LET(z,
y-26+x,
IF((z>0)*(z+x<28),
CHAR(
92-z-x
),
""))))
or
=MAKEARRAY(26,
51,
LAMBDA(x,
y,
LET(z,
y+x,
IF((z>26)*(z+x<54),
CHAR(
118-z-x
),
""))))Excel solution 17 for Create Alphabetic Searchlight Grid, proposed by Sandeep Marwal:
=IF(OR(COLUMN()<=27-ROW(),
COLUMN()>52-2*(ROW()-1)),
"",
CHAR(117-(COLUMN()+2*(ROW()-1))))Excel solution 18 for Create Alphabetic Searchlight Grid, proposed by Tyler Cameron:
=LET(
a,
SEQUENCE(
26,
,
26,
-1
),
b,
REPT(
" ,",
a
),
c,
MAP(
a,
LAMBDA(
x,
TEXTJOIN(
",",
TRUE,
CHAR(
SORT(
SEQUENCE(
,
x,
65
),
,
-1,
TRUE
)
)
)
)
),
IFERROR(
DROP(
REDUCE(
"",
b&c,
LAMBDA(
t,
u,
VSTACK(
t,
TEXTSPLIT(
u,
","
)
)
)
& ),
1
),
""
)
)Solving the challenge of Create Alphabetic Searchlight Grid with Python
Python solution 1 for Create Alphabetic Searchlight Grid, proposed by Konrad Gryczan, PhD:
import pandas as pd
import numpy as np
import openpyxl
def shift_left(mat, shift_size):
n_cols = mat.shape[1]
df = pd.DataFrame(mat)
shifted_df = df.apply(lambda row: pd.Series(np.roll(row, -shift_size)), axis=1)
shifted_df = shifted_df.iloc[:, :n_cols]
return shifted_df.values
# make dict of letters to numbers
letters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
letter_dict = {}
for i in range(26):
letter_dict[letters[i]] = i+1
M_final = None
for i in range(26, 0, -1):
M = np.full((1, 52), np.nan)
M[0, :i] = np.arange(1, i+1)
M = np.flip(M, axis=1)
M = shift_left(M, 53-2*i)
if i == 26:
M_final = M
else:
M_final = np.vstack((M_final, M))
mf_df = pd.DataFrame(M_final)
mf_df = mf_df.applymap(lambda x: letters[int(x)-1] if not np.isnan(x) else '')
Solving the challenge of Create Alphabetic Searchlight Grid with Python in Excel
Python in Excel solution 1 for Create Alphabetic Searchlight Grid, proposed by Abdallah Ally:
import pandas as pd
n = 26
letters = [chr(x) for x in range(n + 64, n + 38, -1)]
values = []
for i in range(1, n + 1):
values.append([''] * (n - i + 1) + letters[i - 1: ] + [''] * (i - 1) )
df = pd.DataFrame(values)
df.to_excel('Excel_Challenge_430 - Alphabetic Searchlight.xlsx', index=False)
print(df.iloc[:26, :26])
Solving the challenge of Create Alphabetic Searchlight Grid with R
R solution 1 for Create Alphabetic Searchlight Grid, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
library(writexl)
shift_left <- function(mat, shift_size) {
n_cols <- ncol(mat)
tibble_mat <- as_tibble(mat)
shifted_df <- tibble_mat %>%
pmap_dfr(., ~{
row_values <- c(...)
shifted_values <- c(row_values[(shift_size + 1):length(row_values)], rep(NA, shift_size))
return(as_tibble(t(shifted_values)))
})
as.matrix(shifted_df)
}
for (i in 26:1) {
M <- matrix(NA, nrow = 1, ncol = 52)
M[1, 1:i] <- 1:i
M <- t(apply(M, 1, rev))
M = shift_left(M, 53-2*i)
M[!is.na(M)] <- LETTERS[M[!is.na(M)]]
if (i == 26){
M_final <- M
} else {
M_final <- rbind(M_final, M)
}
}
mf_df = M_final %>% as.data.frame()
write_xlsx(mf_df, "430 Excel solution.xlsx")
&&