Understand the pattern and generate it.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 647
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Pattern Recognition Generator with Power Query
Power Query solution 1 for Pattern Recognition Generator, proposed by Kris Jaganah:
let
A = List.Transform(
{0 .. 24},
each List.Combine({List.Repeat({""}, _ + 2), List.Repeat({{"B" .. "Z"}{_}}, 24 - _)})
),
B = List.Transform({0 .. 25}, each List.Repeat({{"A" .. "Z"}{_}}, _)),
C = Table.FromColumns(
{List.Repeat({"A"}, 26)}
& List.RemoveLastN(
List.Combine(
List.Transform(
{0 .. 24},
each {
List.ReplaceValue(List.Zip(B){_}, null, "", (x, y, z) => if x = y then z else x),
A{_}
}
)
)
)
)
in
CPower Query solution 2 for Pattern Recognition Generator, proposed by Abdallah Ally:
let
a = {"A" .. "Z"},
b = List.Repeat,
c = List.Transform(
{0 .. 25},
each
if _ = 0 then
List.Zip({b({"A"}, 26), {null} & List.Skip(a)})
else
List.Zip({b({null}, _ + 1) & b({a{_}}, 25 - _), b({null}, _ + 1) & List.Skip(a, _ + 1)})
),
Result = Table.FromRows(List.Transform(List.Zip(c), each List.RemoveLastN(List.Combine(_), 2)))
in
ResultPower Query solution 3 for Pattern Recognition Generator, proposed by Ramiro Ayala Chávez:
let
a = {"A" .. "Z"},
LR = List.Repeat,
b = List.RemoveLastN(
List.Generate(
() => [i = 0],
each [i] < 26,
each [i = [i] + 1],
each if [i] <> 0 then LR({null}, [i] + 2) & LR({a{[i]}}, 26 - [i]) else LR({a{[i]}}, 28 - [i])
)
),
c = List.Generate(
() => [i = 1],
each [i] < 26,
each [i = [i] + 1],
each LR({null}, [i] + 1) & List.Range(a, [i])
),
Sol = Table.RemoveLastN(Table.Skip(Table.FromColumns(List.Combine(List.Zip({b, c})))))
in
SolPower Query solution 4 for Pattern Recognition Generator, proposed by Seokho MOON:
let
Cols = List.Generate(
() => [n = 0, l = {}],
each 25 - [n] >= 0,
each
[
n = [n] + 1,
l = {List.Repeat({{"A".."Z"}{[n]}}, 25 - [n]), List.Range(List.Reverse({"A".."Z"}), 0, 25 - [n])}
],
each [l]
),
Res = hashtag#table({"Column1"}, {{"A"}}) & Table.ReverseRows(Table.FromColumns(List.Combine(Cols)))
in
Res
Power Query solution 5 for Pattern Recognition Generator, proposed by Peter Krkos:
let
L = {"A" .. "Z"},
Gen = List.Accumulate(
{1 .. List.Count(L) - 1},
{},
(s, c) =>
s
& {
List.Repeat({null}, c) & List.Skip(L, c),
List.Repeat({null}, c + 1) & List.Repeat({L{c}}, List.Count(L) - c - 1)
}
),
ToTable = Table.FromColumns({List.Repeat({L{0}}, List.Count(L))} & List.RemoveLastN(Gen, 1))
in
ToTableSolving the challenge of Pattern Recognition Generator with Excel
Excel solution 1 for Pattern Recognition Generator, proposed by Bo Rydobon 🇹🇭:
=MAKEARRAY(26,50,LAMBDA(r,c,REPT(CHAR(IF(MOD(c,2),c/2,r-1)+65),r*2>c+(r>1))))Excel solution 2 for Pattern Recognition Generator, proposed by Rick Rothstein:
=DROP(
REDUCE(
"",
SEQUENCE(
26
),
LAMBDA(
a,
x,
LET(
s,
SEQUENCE(
27-x
),
HSTACK(
a,
VSTACK(
IF(
x=1,
{"A",
""},
TAKE(
IF(
SEQUENCE(
26
),
{"",
""}
),
x
)
),
HSTACK(
IF(
s,
CHAR(
64+x
)
),
CHAR(
s+64+x
)
)
)
)
)
)
),
-1,
1
)Excel solution 3 for Pattern Recognition Generator, proposed by John V.:
=MAKEARRAY(26,50,LAMBDA(r,c,REPT(CHAR(64+IF(ISODD(c),1+c/2,r)),c+(r>1)<2*r)))Excel solution 4 for Pattern Recognition Generator, proposed by Kris Jaganah:
=MAKEARRAY(26,50,LAMBDA(x,y,IFS(y=1,"A",((2*x-y)>1)*ISODD(y),CHAR(y/2+65),(2*x-y-1)>0,CHAR(x+64),1,"")))Excel solution 5 for Pattern Recognition Generator, proposed by Aditya Kumar Darak 🇮🇳:
=MAKEARRAY(26, 50, LAMBDA(r,c, IFS(r * c = 1, "A", c > 2 * (r - 1), "", ISODD(c), CHAR(64 + (c + 1) / 2), 1, CHAR(64 + r))))Excel solution 6 for Pattern Recognition Generator, proposed by Timothée BLIOT:
=MAKEARRAY(26,50,LAMBDA(x,y,IF(OR(y=1,x-1>=y/2),CHAR(IF(ISODD(y),(y+1)/2+64,x+64)),"")))Excel solution 7 for Pattern Recognition Generator, proposed by Sunny Baggu:
=LET(
_r, SEQUENCE(, 50),
_ra, N(ISODD(--_r)),
_rb, SCAN(0, _ra, LAMBDA(a, v, a + v)),
_c, SEQUENCE(26),
_ca, VSTACK(1, SEQUENCE(25, , 2, 2)),
IF(
_ca >= _r,
CHAR(64 + IF(_c, (1 - _ra) * _c) + IF(_c, _rb * _ra)),
""
)
)Excel solution 8 for Pattern Recognition Generator, proposed by LEONARD OCHEA 🇷🇴:
=MAKEARRAY(26,50,LAMBDA(f,c,LET(d,INT((c-1)/2),CHAR(IF(f*c=1,65,IF(dExcel solution 9 for Pattern Recognition Generator, proposed by Pieter de B.:
=MAKEARRAY(26,50,LAMBDA(r,c,IF(c-1,IF((c+2)/2>r,"",CHAR(64+IF(ISODD(c),(c+1)/2,r))),"A")))Excel solution 10 for Pattern Recognition Generator, proposed by ferhat CK:
=LET(
a,
CHAR(
SEQUENCE(
26,
,
65
)
),
r,
LAMBDA(
x,
REGEXEXTRACT(
x,
".",
1
)
),
b,
IFERROR(
SCAN(
"",
SEQUENCE(
26
),
LAMBDA(
x,
y,
CONCAT(
r(
TAKE(
a,
y-1
)
)&CHOOSEROWS(
a,
y
)
)
)
),
"A"
),
IFERROR(
DROP(
REDUCE(
0,
b,
LAMBDA(
x,
y,
VSTACK(
x,
r(
y
)
)
)
),
1
),
""
)
)Excel solution 11 for Pattern Recognition Generator, proposed by Jaroslaw Kujawa:
=MAKEARRAY(26;50;LAMBDA(r;c;IF((r=1)*(c=1);"A";IF(c>2*r-2;"";IF(ISODD(c);CHAR(64+ROUNDUP(c/2;));CHAR(64+r))))))Solving the challenge of Pattern Recognition Generator with Python
Python solution 1 for Pattern Recognition Generator, proposed by Konrad Gryczan, PhD:
import numpy as np
import string
import pandas as pd
path = "647 Alphabets Pattern Generation.xlsx"
test = pd.read_excel(path, header=None, usecols="A:AX", skiprows=1, nrows=26).fillna("")
def generate_matrix(rows=26, cols=50):
mat = np.full((rows, cols), "", dtype=object)
mat[:, 0] = "A"
letters = np.array(list(string.ascii_uppercase))
for i in range(1, cols):
if (i + 1) % 2 == 0:
start_row = (i + 1) // 2
if start_row < rows:
mat[start_row:, i] = letters[start_row:rows]
else:
start_row = (i + 2) // 2 + 1
if start_row <= rows:
mat[start_row-1:, i] = letters[start_row - 2]
return mat
result = generate_matrix(26, 50)
df = pd.DataFrame(result)
print(df.equals(test)) # True
Solving the challenge of Pattern Recognition Generator with Python in Excel
Python in Excel solution 1 for Pattern Recognition Generator, proposed by Aditya Kumar Darak 🇮🇳:
rows, cols = 26, 50
r, c = np.meshgrid(np.arange(1, rows + 1), np.arange(1, cols + 1), indexing="ij")
cond = [r * c == 1, c > 2 * (r - 1), c % 2 == 1]
choice = [1, 0, (c + 1) // 2]
v = np.select(cond, choice, r)
result = np.where(v > 0, np.vectorize(chr)(64 + v), "")
Solving the challenge of Pattern Recognition Generator with R
R solution 1 for Pattern Recognition Generator, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path <- "Excel/647 Alphabets Pattern Generation.xlsx"
test <- read_excel(path, range = "R2C1:R27C50", col_names = FALSE) %>%
as.matrix() %>% replace(is.na(.), "")
generate_matrix <- function(rows = 26, cols = 50) {
mat <- matrix("", nrow = rows, ncol = cols)
mat[, 1] <- "A"
for (i in 2:cols) {
if (i %% 2 == 0) {
mat[(i / 2 + 1):rows, i] <- LETTERS[(i / 2 + 1):rows]
} else {
start_row <- (i + 1) / 2 + 1
if (start_row <= rows) {
mat[start_row:rows, i] <- LETTERS[start_row - 1]
}
}
}
return(mat)
}
result <- generate_matrix(26, 50)
all.equal(result, test, check.attributes = FALSE)
# [1] TRUE
Solving the challenge of Pattern Recognition Generator with Excel VBA
Excel VBA solution 1 for Pattern Recognition Generator, proposed by Nicolas Micot:
Sub challenge_637()
Dim codeDep, codeFin, colDep, ligDep, codeCar
codeDep = 65 'A
codeFin = 90 'Z
colDep = 1
ligDep = 3
col = colDep
Cells(2, 1) = "A"
For codeCar = codeDep To codeFin - 1
dec = 1
For lig = ligDep + (codeCar - codeDep) To codeFin - codeDep + 2
Cells(lig, col) = Chr(codeCar)
Cells(lig, col + 1) = Chr(codeCar + dec)
dec = dec + 1
Next lig
col = col + 2
Next codeCar
End Sub
&&&