GADERYPOLUKI Cipher Column A needs to be changed on the basis of key given in column B. If Key is GA-DE-RY-PO-LU-KI, then G will be changed to A, D will be changed to E…K will be changed to I. Key is case-insensitive. Hence, g>>a, d>>e….k>>i for this key. All other characters / alphabets will be left unchanged. Answer is case sensitive.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 109
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of GADERYPOLUKI Cipher Apply with Power Query
Power Query solution 1 for GADERYPOLUKI Cipher Apply, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.TransformRows(
Source,
each Text.Combine(
List.ReplaceMatchingItems(
Text.ToList([Sentence]),
List.Transform(Text.Split([Key] & "-" & Text.Lower([Key]), "-"), Text.ToList)
)
)
)
in
AnsPower Query solution 2 for GADERYPOLUKI Cipher Apply, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.TransformRows(
Source,
each
let
U = Text.Split([Key], "-"),
Ul = List.Transform(U, each Text.Start(_, 1)),
Ur = List.Transform(U, each Text.End(_, 1)),
L = Text.Split(Text.Lower([Key]), "-"),
Ll = List.Transform(L, each Text.Start(_, 1)),
Lr = List.Transform(L, each Text.End(_, 1))
in
Text.Combine(
List.Transform(
Text.ToList([Sentence]),
each
let
Un = List.PositionOf(Ul, _),
Ln = List.PositionOf(Ll, _)
in
if Un >= 0 then Ur{Un} else if Ln >= 0 then Lr{Ln} else _
)
)
)
in
BPower Query solution 3 for GADERYPOLUKI Cipher Apply, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Listas = Table.TransformColumns(
Source,
{
{"Sentence", each Text.ToList(_)},
{
"Key",
each List.Transform(
Text.Split(_, "-") & Text.Split(Text.Lower(_), "-"),
each Text.ToList(_)
)
}
}
),
Solucion = Table.AddColumn(
Listas,
"Answer",
each Text.Combine(List.ReplaceMatchingItems([Sentence], [Key]), "")
)[[Answer]]
in
SolucionPower Query solution 4 for GADERYPOLUKI Cipher Apply, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
Result = Table.AddColumn(
Fonte,
"Personalizar",
each [
a = Text.Combine(Text.Split([Key], "-")),
b = List.Split(Text.ToList(a) & Text.ToList(Text.Lower(a)), 2),
c = Text.ToList([Sentence]),
d = Text.Combine(List.ReplaceMatchingItems(c, b))
][d]
)[[Personalizar]]
in
ResultPower Query solution 5 for GADERYPOLUKI Cipher Apply, proposed by Bhavya Gupta:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ExpectedOutput = Table.AddColumn(
Source,
"Answer",
each
let
a = Text.Split([Key], "-")
in
Text.Combine(
List.ReplaceMatchingItems(
Text.ToList([Sentence]),
List.Transform(a & List.Transform(a, each Text.Lower(_)), each Text.ToList(_))
)
)
)
in
ExpectedOutputPower Query solution 6 for GADERYPOLUKI Cipher Apply, proposed by Bhavya Gupta:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ExpectedOutput = Table.AddColumn(
Source,
"Answer",
each
let
a = Text.Split([Key] & "-" & Text.Lower([Key]), "-")
in
Text.Combine(
List.ReplaceMatchingItems(
Text.ToList([Sentence]),
List.Transform(a, Text.ToList),
each Text.ToList(_)
)
)
)
in
ExpectedOutputPower Query solution 7 for GADERYPOLUKI Cipher Apply, proposed by Matthias Friedmann:
let
Source = Excel.CurrentWorkbook(){[Name = "CharacterChange"]}[Content],
Replaced = Table.ReplaceValue(Source, "-", "", Replacer.ReplaceText, {"Key"}),
Added = Table.AddColumn(
Replaced,
"Changed",
each Text.Combine(
List.ReplaceMatchingItems(
Text.ToList([Sentence]),
List.Split(Text.ToList([Key] & Text.Lower([Key])), 2)
)
)
)[[Changed]]
in
AddedPower Query solution 8 for GADERYPOLUKI Cipher Apply, proposed by Victor Wang:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.ReplaceValue(
Source,
each true,
each List.Transform(Text.Split([Key] & "-" & Text.Lower([Key]), "-"), Text.ToList),
(a, b, c) => Text.Combine(List.ReplaceMatchingItems(Text.ToList(a), c)),
{"Sentence"}
)[Sentence]
in
ResultSolving the challenge of GADERYPOLUKI Cipher Apply with Excel
Excel solution 1 for GADERYPOLUKI Cipher Apply, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A7,
B2:B7,
LAMBDA(
s,
k,
SUBSTITUTE(
REDUCE(
CONCAT(
"♥"&MID(
s,
SEQUENCE(
LEN(
s
)
),
1
)
),
TEXTSPLIT(
k&"-"&LOWER(
k
),
"-"
),
LAMBDA(
a,
v,
SUBSTITUTE(
a,
"♥"&LEFT(
v
),
RIGHT(
v
)
)
)
),
"♥",
)
)
)Excel solution 2 for GADERYPOLUKI Cipher Apply, proposed by John V.:
=MAP(
A2:A7,
B2:B7,
LAMBDA(
x,
y,
LET(
s,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
v,
VLOOKUP(
s,
MID(
y,
SEQUENCE(
6,
,
,
3
)+{0,
1},
1
),
2,
),
CONCAT(
IFNA(
IF(
EXACT(
UPPER(
s
),
s
),
v,
LOWER(
v
)
),
s
)
)
)
)
)Excel solution 3 for GADERYPOLUKI Cipher Apply, proposed by محمد حلمي:
=MAP(
A2:A7,
B2:B7,
LAMBDA(
a,
b,
LET(
s,
SEQUENCE(
6,
,
,
3
),
r,
REDUCE(
a,
MID(
b,
s,
1
)&" "&MID(
b,
s+1,
1
),
LAMBDA(
a,
x,
SUBSTITUTE(
UPPER(
a
),
TEXTBEFORE(
x,
" "
),
TEXTAFTER(
x,
" "
)
)
)
),
v,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
rr,
MID(
r,
SEQUENCE(
LEN(
r
)
),
1
),
CONCAT(
IF(
EXACT(
v,
UPPER(
v
)
),
rr,
LOWER(
rr
)
)
)
)
)
)
/////
I benefited from a solution Taeyong S.
in use sort
So that the original letter does not change twice
=MAP(
A2:A7,
B2:B7,
LAMBDA(
a,
b,
LET(
s,
SEQUENCE(
6,
,
,
3
),
r,
REDUCE(
a,
SORT(
MID(
b,
s,
1
)&" "&MID(
b,
s+1,
1
),
,
-1
),
LAMBDA(
a,
x,
SUBSTITUTE(
UPPER(
a
),
TEXTBEFORE(
x,
" "
),
TEXTAFTER(
x,
" "
)
)
)
),
v,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
rr,
MID(
r,
SEQUENCE(
LEN(
r
)
),
1
),
CONCAT(
IF(
EXACT(
v,
UPPER(
v
)
),
rr,
LOWER(
rr
)
)
)
)
)
)Excel solution 4 for GADERYPOLUKI Cipher Apply, proposed by 🇰🇷 Taeyong Shin:
=MAP(
A2:A7,
B2:B7,
LAMBDA(
a,
b,
LET(
k,
MID(
TEXTSPLIT(
b,
,
"-"
),
{1,
2},
1
),
tbl,
SORT(
VSTACK(
k,
LOWER(
k
)
),
,
-1
),
REDUCE(
a,
SEQUENCE(
ROWS(
tbl
)
),
LAMBDA(
a,
n,
SUBSTITUTE(
a,
INDEX(
tbl,
n,
1
),
INDEX(
tbl,
n,
2
)
)
)
)
)
)
)
********************************************************************
=MAP(
A2:A7,
B2:B7,
LAMBDA(
a,
b,
LET(
k,
SORT(
TEXTSPLIT(
b & "-" & LOWER(
b
),
,
"-"
),
,
-1
),
REDUCE(
a,
SEQUENCE(
ROWS(
k
)
),
LAMBDA(
str,
n,
SUBSTITUTE(
str,
@LEFT(
INDEX(
k,
n
)
),
@RIGHT(
INDEX(
k,
n
)
)
)
)
)
)
)
)Excel solution 5 for GADERYPOLUKI Cipher Apply, proposed by 🇰🇷 Taeyong Shin:
=MAP(
A2:A7,
B2:B7,
LAMBDA(
a,
b,
LET(
m,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
t,
TEXTSPLIT(
b&"-"&LOWER(
b
),
,
"-"
),
CONCAT(
IFNA(
XLOOKUP(
"(?-i)"&m,
LEFT(
t
),
RIGHT(
t
),
,
3
),
m
)
)
)
)
)Excel solution 6 for GADERYPOLUKI Cipher Apply, proposed by 🇰🇷 Taeyong Shin:
=MAP(
A2:A7,
"-"&B2:B7,
LAMBDA(
x,
y,
LET(
c,
REGEXEXTRACT(
x,
".",
1
),
CONCAT(
IFNA(
REGEXEXTRACT(
y&LOWER(
y
),
"(?<=-"&c&").",
1
),
c
)
)
)
)
)Excel solution 7 for GADERYPOLUKI Cipher Apply, proposed by Julian Poeltl:
=MAP(
A2:A7,
B2:B7,
LAMBDA(
S,&
K,
LET(
SP,
TEXTSPLIT(
K,
"-"
),
F,
LEFT(
SP
),
T,
RIGHT(
SP
),
FF,
HSTACK(
F,
LOWER(
F
)
),
TT,
HSTACK(
T,
LOWER(
T
)
),
REDUCE(
S,
FF&","&TT,
LAMBDA(
A,
B,
SUBSTITUTE(
A,
TEXTBEFORE(
B,
","
),
TEXTAFTER(
B,
","
)
)
)
)
)
)
)Excel solution 8 for GADERYPOLUKI Cipher Apply, proposed by Aditya Kumar Darak 🇮🇳:
=MAP(
A2:A7,
B2:B7,
LAMBDA(
a,
b,
LET(
sk,
TEXTSPLIT(
b,
,
"-"
),
ch1,
LEFT(
sk
),
ch2,
RIGHT(
sk
),
ss,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
rp,
IFNA(
XLOOKUP(
ss,
ch1,
ch2
),
ss
),
c,
IF(
EXACT(
UPPER(
ss
),
ss
),
rp,
LOWER(
rp
)
),
r,
CONCAT(
c
),
r
)
)
)Excel solution 9 for GADERYPOLUKI Cipher Apply, proposed by Timothée BLIOT:
=MAP(
A2:A7,
B2:B7,
LAMBDA(
v,
w,
LET(
A,
DROP(
REDUCE(
"",
TOCOL(
TEXTSPLIT(
w,
"-"
)
),
LAMBDA(
a,
s,
VSTACK(
a,
MID(
s,
{1,
2},
1
)
)
)
),
1
),
B,
MID(
v,
SEQUENCE(
LEN(
v
)
),
1
),
D,
IF(
EXACT(
B,
UPPER(
B
)
),
1,
0
),
E,
MAP(
UPPER(
B
),
LAMBDA(
x,
XLOOKUP(
x,
TAKE(
A,
,
1
),
TAKE(
A,
,
-1
),
x
)
)
),
CONCAT(
MAP(
SEQUENCE(
ROWS(
E
)
),
LAMBDA(
x,
IF(
INDEX(
D,
x
)=1,
UPPER(
INDEX(
E,
x
)
),
LOWER(
INDEX(
E,
x
)
)
)
)
)
)
)
)
)Excel solution 10 for GADERYPOLUKI Cipher Apply, proposed by Md. Zohurul Islam:
=MAP(
A2:A7,
B2:B7,
LAMBDA(
x,
y,
LET(
a,
TOCOL(
TEXTSPLIT(
y,
"-"
)
),
b,
LEFT(
a
),
c,
RIGHT(
a
),
d,
LOWER(
b
),
e,
LOWER(
c
),
f,
VSTACK(
CODE(
b
),
CODE(
d
)
),
g,
VSTACK(
CODE(
c
),
CODE(
e
)
),
h,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
j,
CODE(
h
),
k,
XLOOKUP(
j,
f,
g
),
l,
CHAR(
IFNA(
k,
j
)
),
m,
CONCAT(
l
),
m
)
)
)Excel solution 11 for GADERYPOLUKI Cipher Apply, proposed by Charles Roldan:
=LET(
_TEXTTOARRAY,
LAMBDA(
x,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
),
MAP(
A2:A7,
B2:B7,
LAMBDA(
Sentence,
Key,
LET(
x,
CODE(
WRAPCOLS(
_TEXTTOARRAY(
Key&"-"&LOWER(
Key
)
),
3
)
),
REDUCE(
"",
CODE(
_TEXTTOARRAY(
Sentence
)
),
LAMBDA(
a,
b,
a&CHAR(
XLOOKUP(
b,
INDEX(
x,
1
),
INDEX(
x,
2
),
b
)
)
)
)
)
)
)
)Excel solution 12 for GADERYPOLUKI Cipher Apply, proposed by Stefan Olsson:
=MAP(
A2:A7,
B2:B7,
LAMBDA(
s,
k,
REDUCE(
"",
SPLIT(
REGEXREPLACE(
s,
"(.)",
"$1|"
),
"|",
true,
true
),
LAMBDA(
i,
x,
i&IFNA(
REGEXEXTRACT(
k&"-"&LOWER(
k
),
x&"(w)"
),
x
)
)
)
)
)Excel solution 13 for GADERYPOLUKI Cipher Apply, proposed by Julien Lacaze:
=MAP(
A2:A7,
B2:B7,
LAMBDA(
data,
key,
LET(
convert,
TEXTSPLIT(
key,
,
"-"
),
source,
CODE(
VSTACK(
LEFT(
convert
),
LOWER(
LEFT(
convert
)
)
)
),
target,
VSTACK(
RIGHT(
convert
),
LOWER(
RIGHT(
convert
)
)
),
split,
MID(
data,
SEQUENCE(
LEN(
data
)
),
1
),
CONCAT(
IFERROR(
XLOOKUP(
CODE(
split
),
source,
target
),
split
)
)
)
)
)Excel solution 14 for GADERYPOLUKI Cipher Apply, proposed by Abhishek Kumar Jain:
=MAP(A2:A7,
B2:B7,
LAMBDA(x,
y,
LET(a,
MID(
x,
SEQUENCE(
,
LEN(
x
)
),
1
),
b,
LEFT(
TEXTSPLIT(
y,
"-"
)
),
c,
RIGHT(
TEXTSPLIT(
y,
"-"
)
),
d,
XLOOKUP(
a,
b,
c,
""
),
TEXTJOIN("",
FALSE,
IFS(d="",
a,
(CODE(
a
)>=65)*(CODE(
a
)<=90)=0,
LOWER(
d
),
TRUE,
d)))))Excel solution 15 for GADERYPOLUKI Cipher Apply, proposed by Guillermo Arroyo:
=BYROW(
A2:B7,
LAMBDA(
a,
LET(
s,
INDEX(
a,
,
1
),
k,
MID(
TEXTSPLIT(
INDEX(
a,
,
2
),
,
"-"
),
{1,
2},
1
),
l,
LEN(
s
),
r,
LAMBDA(
m,
b,
c,
LET(
d,
MID(
s,
b,
1
),
e,
IFERROR(
INDEX(
k,
MATCH(
d,
CHOOSECOLS(
k,
1
),
0
),
2
),
d
),
f,
IF(
CODE(
d
)>=97,
LOWER(
e
),
UPPER(
e
)
),
IF(
b>l,
c,
m(
m,
b+1,
c&f
)
)
)
),
r(
r,
1,
""
)
)
)
)Solving the challenge of GADERYPOLUKI Cipher Apply with SQL
SQL solution 1 for GADERYPOLUKI Cipher Apply, proposed by Zoran Milokanović:
WITH /* Microsoft SQL Server 2019 */
DATA_PREP
AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ORDINAL_NUMBER
,D.SENTENCE
,D."KEY"
FROM DATA D
)
SELECT
F.SENTENCE
,F."KEY"
,TRANSLATE(TRANSLATE(F.SENTENCE, UPPER(F.KEY_START), UPPER(F.KEY_END)), LOWER(F.KEY_START), LOWER(F.KEY_END)) AS ANSWER
FROM
(
SELECT
DP.ORDINAL_NUMBER
,DP.SENTENCE AS SENTENCE
,"KEY"
,STRING_AGG(SUBSTRING(VALUE, 1, 1), '') AS KEY_START
,STRING_AGG(SUBSTRING(VALUE, 2, 1), '') AS KEY_END
FROM DATA_PREP DP
CROSS APPLY STRING_SPLIT(DP."KEY", '-')
GROUP BY
DP.ORDINAL_NUMBER
,DP.SENTENCE
,"KEY"
) F
ORDER BY
F.ORDINAL_NUMBER
;
&&