List the String1 and String2 if they are rotated versions of each other. Ex. String1 = planet, String2 = netpla String1 = jupiter, String2 = rjupite 0 rotation is not to be considered. Hence, planet and planet will not be a valid answer.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 449
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Identify Rotated String Matches with Power Query
Power Query solution 1 for Identify Rotated String Matches, proposed by Rick de Groot:
let
Source = Table1,
SelRow = Table.SelectRows(Source, each let ttl = (x) => Text.ToList(x), ls = (y) => List.Sort(y),
List1 = ls( ttl([String1])),
List2 = ls( ttl([String2])),
Test = Text.Contains( [String1] & [String1], [String2]),
Result = List1 = List2 and [String1] <> [String2] and Test in Result)
in
SelRow
hashtag#BIGorilla hashtag#powerquery hashtag#powerqueryhow hashtag#powerbi
Power Query solution 2 for Identify Rotated String Matches, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Return = Table.SelectRows(
Source,
each Text.Contains([String1] & [String1], [String2])
and Text.Length([String1])
= Text.Length([String2]) and [String1]
<> [String2]
)
in
ReturnPower Query solution 3 for Identify Rotated String Matches, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.SelectRows(
Source,
each
if [String1] = [String2] then
false
else
Text.Contains(Text.Repeat([String1], 2), [String2])
)
in
SolPower Query solution 4 for Identify Rotated String Matches, proposed by Venkata Rajesh:
let
Source = Data,
Output = Table.SelectRows(
Source,
each [
x = Text.PositionOf([String2], Text.Start([String1], 1), Occurrence.Last),
y = Text.Middle([String2], x) & Text.Start([String2], x),
z = if x = 0 then false else if [String1] = y then true else false
][z]
)
in
OutputSolving the challenge of Identify Rotated String Matches with Excel
Excel solution 1 for Identify Rotated String Matches, proposed by Bo Rydobon 🇹🇭:
=FILTER(
A2:B10,
IFERROR(
FIND(
B2:B10,
A2:A10&A2:A10
)-1,
0
)
)
More solid with check Len
=LET(a,
A2:A10,
b,
B2:B10,
FILTER(A2:B10,
IFERROR(
FIND(
b,
a&a
)-1,
0
)*(LEN(
a
)=LEN(
b
))))Excel solution 2 for Identify Rotated String Matches, proposed by Rick Rothstein:
=LET(a,
A2:A10,
b,
B2:B10,
FILTER(A2:B10,
(a<>b)*ISNUMBER(
FIND(
a,
b&b
)
)))
=LET(a,
A2:A10,
b,
B2:B10,
FILTER(A2:B10,
IFERROR((a<>b)*FIND(
a,
b&b
),
)))Excel solution 3 for Identify Rotated String Matches, proposed by John V.:
=LET(a,
A2:A10,
b,
B2:B10,
FILTER(A2:B10,
(a<>b)-ISERR(
FIND(
b,
a&a
)
)))Excel solution 4 for Identify Rotated String Matches, proposed by محمد حلمي:
=FILTER(
A2:B10,
IFERROR(
MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
LET(
i,
MID(
a,
SEQUENCE(
20
),
1
),
s,
XMATCH(
LEFT(
b
),
i
)-1,
b=CONCAT(
DROP(
i,
s
),
TAKE(
i,
s
)
)
)
)
),
)
)Excel solution 5 for Identify Rotated String Matches, proposed by Kris Jaganah:
=LET(
p,
A2:A10,
q,
B2:B10,
FILTER(
HSTACK(
p,
q
),
--ISERR(
MAP(
p,
q,
LAMBDA(
x,
y,
LET(
a,
LEN(
x
),
b,
SEQUENCE(
a-1
),
c,
RIGHT(
x,
b
)&LEFT(
x,
a-b
),
FILTER(
c,
c=y
)
)
)
)
)=0
)
)Excel solution 6 for Identify Rotated String Matches, proposed by Timothée BLIOT:
=FILTER(A2:B10,
(LEN(
A2:A10
)=LEN(
B2:B10
))*(ISNUMBER(
FIND(
B2:B10,
A2:A10&A2:A10
)
))*(A2:A10<>B2:B10))Excel solution 7 for Identify Rotated String Matches, proposed by Hussein SATOUR:
=FILTER(
A2:B10,
MAP(
A2:A10,
B2:B10,
LAMBDA(
y,
z,
LET(
b,
LEN(
y
),
SUM(
MAP(
DROP(
SEQUENCE(
b
),
1
),
LAMBDA(
x,
CONCAT(
MID(
y,
LET(
a,
x+SEQUENCE(
,
b,
0
),
IF(
a>b,
a-b,
a
)
),
1
)
)=z
)
)*1
)
)
)
)
)Excel solution 8 for Identify Rotated String Matches, proposed by Sunny Baggu:
=LET(
_s1,
A2:A10,
_s2,
B2:B10,
_c1,
_s1 <> _s2,
_c2,
ISNUMBER(
SEARCH(
_s2,
_s1 & _s1
)
),
FILTER(
A2:B10,
_c1 * _c2
)
)Excel solution 9 for Identify Rotated String Matches, proposed by LEONARD OCHEA 🇷🇴:
=FILTER(
A2:B10,
MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
LET(
l,
LEN(
a
),
s,
SEQUENCE(
l-1
),
OR(
RIGHT(
a,
l-s
)&LEFT(
a,
s
)=b
)
)
)
)
)Excel solution 10 for Identify Rotated String Matches, proposed by Abdallah Ally:
=FILTER(A2:B10,
MAP(A2:A10,
B2:B10,
LAMBDA(u,
v,
LET(a,
u,
b,
v,
c,
LEN(
b
),
(u<>v)*REDUCE(0,
SEQUENCE(
c
),
LAMBDA(x,
y,
x+(RIGHT(
b,
c-y
)&LEFT(
b,
y
)=a)))))))Excel solution 11 for Identify Rotated String Matches, proposed by Andy Heybruch:
=LET(_text,
A2:A10&"|"&B2:B10,
_filter,
MAP(_text,
LAMBDA(_string,
LET(
_string1,
TEXTBEFORE(
_string,
"|"
),
_string2,
TEXTAFTER(
_string,
"|"
),
_len,
LEN(
_string1
),
_iterations,
SCAN("",
SEQUENCE(
_len
),
LAMBDA(a,
v,
HSTACK(TEXTJOIN("|",
,
(MOD(
SEQUENCE(
_len
)-v,
_len
)+1))))),
_char,
MID(
_string1,
SEQUENCE(
LEN(
_string1
)
),
1
),
_chk,
SCAN(
"",
_iterations,
LAMBDA(
a,
v,
LET(
_order,
TEXTSPLIT(
v,
,
"|"
),
CONCAT(
INDEX(
_char,
_order,
)
)
)
)
),
SUM((_chk=_string2)*(_string1<>_string2))))),
TEXTSPLIT(
TEXTJOIN(
";",
,
FILTER(
_text,
_filter,
1
)
),
"|",
";"
))Excel solution 12 for Identify Rotated String Matches, proposed by Burhan Cesur:
=FILTER(A2:B10,
MAP(A2:A10,
B2:B10,
LAMBDA(x,
y,
BYCOL(--(BYROW(--(MID(
x,
DROP(
REDUCE(
"",
SEQUENCE(
LEN(
x
),
,
LEN(
x
),
-1
),
LAMBDA(
s,
v,
VSTACK(
s,
1+MOD(
SEQUENCE(
1,
LEN(
x
),
v
),
LEN(
x
)
)
)
)
),
2
),
1
)=MID(
y,
SEQUENCE(
,
LEN(
y
),
1
),
1
)),
SUM)=LEN(
x
)),
SUM))))Excel solution 13 for Identify Rotated String Matches, proposed by Josh Brodrick:
=LET(
a,
A2:A10,
b,
B2:B10,
c,
MAP(
a,
b,
LAMBDA(
x,
y,
IF(
x=y,
"",
IF(
ISNUMBER(
--CONCAT(
IFERROR(
FIND(
y,
MID(
x,
SEQUENCE(
LEN(
x
),
,
1
),
LEN(
x
)
)&x
),
""
)
)
),
x&" "&y,
""
)
)
)
),
HSTACK(
TOCOL(
TEXTBEFORE(
c,
" "
),
3
),
TOCOL(
TEXTAFTER(
c,
" "
),
3
)
)
)Excel solution 14 for Identify Rotated String Matches, proposed by Tyler Cameron:
=FILTER(
A2:B10,
IFERROR(
FIND(
A2:A10,
REPT(
B2:B10,
2
)
),
1
)>1
)Excel solution 15 for Identify Rotated String Matches, proposed by Tyler Cameron:
=LET(
a,
HSTACK(
A2:A10,
MAP(
A2:A10,
B2:B10,
LAMBDA(
x,
y,
LET(
b,
LEN(
x
),
d,
FIND(
LEFT(
x,
1
),
RIGHT(
y,
b-1
)
),
IFERROR(
IF(
RIGHT(
x,
d
)&LEFT(
x,
b-d
)=y,
y
),
FALSE
)
)
)
)
),
FILTER(
a,
CHOOSECOLS(
a,
2
)<>FALSE
)
)Excel solution 16 for Identify Rotated String Matches, proposed by Will Freestone:
=LET(
a,
A2:A10,
b,
B2:B10,
FILTER(
HSTACK(
a,
b
),
IFERROR(
FIND(
b,
a&a
),
0
)>1
)
)