All words have a one digit number as its last character. Sort the words as per the number. In case of tie between numbers, sorting will have to be done on the basis of words. Ex. “hope1 never9 dies1” Ans. “dies1 hope1 never9”
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 246
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Sort Words by End Digit with Power Query
Power Query solution 1 for Sort Words by End Digit, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.TransformRows(
Source,
each Text.Combine(List.Sort(Text.Split([Sentences], " "), each Text.End(_, 1) & _), " ")
)
in
AnsPower Query solution 2 for Sort Words by End Digit, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.TransformRows(
Source,
each Text.Combine(
List.Sort(Text.Split([Sentences], " "), {{each Text.End(_, 1)}, {each _}}),
" "
)
)
in
SPower Query solution 3 for Sort Words by End Digit, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Sol = Table.TransformColumns(Source, {"Sentences", each
let
a = Text.Split(_, " "),
b = List.Sort( a, {each Text.End(_, 1), each _})
in Text.Combine(b, " ")})
in
Sol
Me base en Rick de Groot: https://gorilla.bi/power-query/how-list-sort-works-in-m/#order-type
Power Query solution 4 for Sort Words by End Digit, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
res = Table.AddColumn(
Fonte,
"Personalizar",
each [
a = Text.Split([Sentences], " "),
b = List.Transform(
a,
each [x = {Text.Select(_, {"0" .. "9"})}, y = List.Combine(List.Zip({{_}, x}))][y]
),
c = Text.Combine(
Table.Sort(Table.FromRows(b), {{"Column2", Order.Ascending}, {"Column1", Order.Ascending}})[
Column1
],
" "
)
][c]
)
in
resPower Query solution 5 for Sort Words by End Digit, proposed by Brian Julius:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddSorters = Table.AddColumn(
Source,
"Answer",
each [
a = Text.Split([Sentences], " "),
b = List.Transform(a, each Number.From(Text.End(_, 1))),
d = Table.FromColumns({a, b}),
e = Table.Sort(d, {{"Column2", Order.Ascending}, {"Column1", Order.Ascending}}),
f = Table.SelectColumns(e, "Column1"),
g = Text.Combine(f[Column1], " ")
][g]
)
in
AddSortersPower Query solution 6 for Sort Words by End Digit, proposed by Rafael González B.:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
TC = Table.TransformColumnTypes(Source, {{"Sentences", type text}}),
Result = Table.AddColumn(
TC,
"Answer",
each
let
a = Text.Split([Sentences], " "),
b = List.Sort(a, {{each Text.End(_, 1)}, {each _, Order.Ascending}}),
c = Text.Combine(b, " ")
in
c
)[[Answer]]
in
ResultPower Query solution 7 for Sort Words by End Digit, proposed by Jonasz Dzido:
let
Source = Excel.CurrentWorkbook(),
Table2 = Source{[Name = "Table2"]}[Content],
#"Added Custom" = Table.AddColumn(
Table2,
"Answer",
each
let
Step1 = Text.Split(_[Sentences], " "), // split the content of Sentences rows by space
Step2 = List.Transform(Step1, each [Word = _, Order = Text.End(_, 1)]), // from each list item, create a record with number extracted
Step3 = Table.Sort(
Table.FromRecords(Step2),
{{"Order", Order.Ascending}, {"Word", Order.Ascending}}
), // Combine and sort records
Step4 = Text.Combine(Step3[Word], " ") // combine sorted values into text
in
Step4
)
in
#"Added Custom"Solving the challenge of Sort Words by End Digit with Excel
Excel solution 1 for Sort Words by End Digit, proposed by Omid Motamedisedeh:
=TEXTJOIN(
" ",
,
SORTBY(
TEXTSPLIT(
A2,
" "
),
RIGHT(
TEXTSPLIT(
A2,
" "
)
)+CODE(
LOWER(
LEFT(
TEXTSPLIT(
A2,
" "
)
)
)
)/1000
)
)Excel solution 2 for Sort Words by End Digit, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A10,
LAMBDA(
a,
LET(
t,
TEXTSPLIT(
a,
" "
),
TEXTJOIN(
" ",
,
SORTBY(
t,
RIGHT(
t
),
,
t,
)
)
)
)
)Excel solution 3 for Sort Words by End Digit, proposed by Rick Rothstein:
=MAP(A2:A10,LAMBDA(x,LET(s,TEXTSPLIT(x," "),TEXTJOIN(" ",,SORTBY(s,RIGHT(s),,s,)))))Excel solution 4 for Sort Words by End Digit, proposed by John V.:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
t,
TEXTSPLIT(
x,
" "
),
TEXTJOIN(
" ",
,
SORTBY(
t,
RIGHT(
t
)&t
)
)
)
)
)Excel solution 5 for Sort Words by End Digit, proposed by محمد حلمي:
=MAP(A2:A10,LAMBDA(a,LET(r,TEXTSPLIT(a," "),TEXTJOIN(" ",,SORTBY(r,RIGHT(r),,r,)))))Excel solution 6 for Sort Words by End Digit, proposed by Kris Jaganah:
=MAP(A2:A10,LAMBDA(x,LET(a,TEXTSPLIT(x,," "),TEXTJOIN(" ",,SORTBY(a,RIGHT(a),1,a,1)))))Excel solution 7 for Sort Words by End Digit, proposed by Julian Poeltl:
=MAP(A2:A10,LAMBDA(A,LET(S,TEXTSPLIT(A," "),TEXTJOIN(" ",,SORTBY(S,--RIGHT(S),,S,)))))Excel solution 8 for Sort Words by End Digit, proposed by Timothée BLIOT:
=MAP(A2:A10,LAMBDA(z,TEXTJOIN(" ",,MAP(SORT(TOCOL(MAP(TEXTSPLIT( z," "),LAMBDA(x,RIGHT(x)&x)))),LAMBDA(x,MID(x,2,LEN(x)-1))))))Excel solution 9 for Sort Words by End Digit, proposed by Hussein SATOUR:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
a,
TEXTSPLIT(
x,
,
" "
),
TEXTJOIN(
" ",
,
SORTBY(
a,
RIGHT(
a
),
,
LEFT(
a
),
)
)
)
)
)Excel solution 10 for Sort Words by End Digit, proposed by Sunny Baggu:
=MAP(
A2:A10,
LAMBDA(
a,
TEXTJOIN(
" ",
,
LET(
_ts,
TEXTSPLIT(
a,
,
" "
),
SORTBY(
_ts,
RIGHT(
_ts
),
,
LEFT(
_ts
),
)
)
)
)
)Excel solution 11 for Sort Words by End Digit, proposed by Abdallah Ally:
=MAP(A2:A10,LAMBDA(x,LET(a,x,b,TEXTSPLIT(a," "),c,RIGHT(b)&LEFT(b,LEN(b)-1),TEXTJOIN(" ",TRUE,SORTBY(b,c)))))Excel solution 12 for Sort Words by End Digit, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=MAP(
A2:A10;
LAMBDA(
y;
TEXTJOIN(
" ";
;
LET(
x;
TEXTSPLIT(
y;
;
" "
);
SORTBY(
x;
x;
1;
SUBSTITUTE(
x;
VALUE(
RIGHT(
x;
1
)
);
"";
1
);
1
)
)
)
)
)Excel solution 13 for Sort Words by End Digit, proposed by Julien Lacaze:
=MAP(
A2:A10,
LAMBDA(
a,
LET(
b,
TEXTSPLIT(
a,
" "
),
TEXTJOIN(
" ",
,
SORTBY(
b,
RIGHT(
b
),
,
b,
)
)
)
)
)Excel solution 14 for Sort Words by End Digit, proposed by Nicolas Micot:
=MAP(A2:A10;LAMBDA(l_sentence;
LET(_words;FRACTIONNER.TEXTE(l_sentence;;" ");
_numbers;DROITE(_words;1)+0;
_texts;SUBSTITUE(_words;_numbers;"");
JOINDRE.TEXTE(" ";;TRIERPAR(_words;_numbers;1;_texts;1)))))Excel solution 15 for Sort Words by End Digit, proposed by Daniel Garzia:
=MAP(A2:A10,LAMBDA(l,LET(t,TEXTSPLIT(l,," "),TEXTJOIN(" ",,SORTBY(t,RIGHT(t),1,t,1)))))Excel solution 16 for Sort Words by End Digit, proposed by Quadri Olayinka Atharu:
=MAP(A2:A10,LAMBDA(x,LET(y,TEXTSPLIT(x," "),TEXTJOIN(" ",1,SORTBY(y,RIGHT(y),,LEFT(y),)))))Excel solution 17 for Sort Words by End Digit, proposed by Md Ismail Hosen:
=LAMBDA(Sentences,
MAP(
Sentences,
LAMBDA(Sentence,
LET(
_Sentence, Sentence,
_Words, TEXTSPLIT(_Sentence, , " "),
_Numbers, RIGHT(_Words, 1) * 1,
_Result, TEXTJOIN(" ", FALSE, SORTBY(_Words, _Numbers, 1, _Words, 1)),
_Result
)
)
)
)(A2:A10)Excel solution 18 for Sort Words by End Digit, proposed by Amardeep Singh:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
t,
TEXTSPLIT(
x,
" "
),
TEXTJOIN(
" ",
,
SORTBY(
t,
--RIGHT(
t
),
,
t,
)
)
)
)
)Excel solution 19 for Sort Words by End Digit, proposed by Henriette Hamer:
=MAP(
A2:A10;
LAMBDA(
_data;
LET(
_text;
_data;
TEXTJOIN(
" ";
TRUE;
MID(
SORT(
RIGHT(
TEXTSPLIT(
_text;
" "
);
1
)&TEXTSPLIT(
_text;
" "
);
;
;
TRUE
);
2;
1000
)
)
)
)
)Excel solution 20 for Sort Words by End Digit, proposed by Jeff Blakley:
=MAP(
A2:A10,
LAMBDA(
txt,
LET(
arr,
TEXTSPLIT(
txt,
" "
),
TEXTJOIN(
" ",
,
SORTBY(
arr,
RIGHT(
arr
),
,
arr,
)
)
)
)
)Excel solution 21 for Sort Words by End Digit, proposed by Harry Seiders:
MAP(A2:A10,LAMBDA(item,LET(A,TEXTSPLIT(item,," "),B,LEFT(A,LEN(A)-1),C,RIGHT(A,1),D,SORTBY(A,C,1,B,1),TEXTJOIN(" ",,D))))&&&