Keep the numbers in even positions as it is and sort the numbers in odd positions. Position will start with index 1 not index 0. Ex. 53214 => 23415
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 574
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Sort Odd Position Values with Power Query
Power Query solution 1 for Sort Odd Position Values, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Return = Table.AddColumn(
Source,
"Answer",
each [
T = Text.From([Numbers]),
Tl = Text.ToList(T),
S = List.Split(Tl, 2),
Z1 = List.Zip(S),
St = {List.Sort(Z1{0})} & {Z1{1}},
Z2 = List.Zip(St),
C = List.Combine(Z2),
R = Text.Combine(C)
][R]
)
in
ReturnPower Query solution 2 for Sort Odd Position Values, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Answer",
each
let
a = Text.From([Numbers]),
b = Text.ToList(a),
c = List.Positions(b),
d = List.Zip({b, c}),
e = List.Select(d, each Number.IsOdd(_{1})),
f = List.Difference(d, e),
g = List.Zip({List.Sort(List.Transform(f, each _{0})), List.Transform(f, each _{1})}),
h = Text.Combine(List.Transform(List.Sort(g & e, each _{1}), each _{0}))
in
h
)[[Answer]]
in
SolPower Query solution 3 for Sort Odd Position Values, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddCol = Table.AddColumn(
Source,
"My Answer",
each [
a = Text.ToList(Text.From([Numbers])),
b = {0 .. List.Count(a) - 1},
c = List.Select(List.Zip({a, b}), each Number.IsEven(_{1})),
d = List.Sort(c, each _{0}),
e = List.Transform(List.Zip({c, d}), each {_{0}{1}, _{1}{1}}),
f = List.Accumulate(
b,
"",
(x, y) => x & (if Number.IsOdd(y) then a{y} else a{List.Select(e, each _{0} = y){0}{1}})
)
][f]
),
Result = Table.AddColumn(AddCol, "Check", each [My Answer] = [Answer Expected])
in
ResultPower Query solution 4 for Sort Odd Position Values, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddCol = Table.AddColumn(
Source,
"My Answer",
each [
a = Text.ToList(Text.From([Numbers])),
b = List.Sort(List.Alternate(a, 1, 1, 1)),
c = List.Alternate(a, 1, 1),
d = Text.Combine(List.Combine(List.Zip({b, c})))
][d]
),
Result = Table.AddColumn(AddCol, "Check", each [My Answer] = [Answer Expected])
in
ResultPower Query solution 5 for Sort Odd Position Values, proposed by Alexandre Garcia:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content][Numbers],
B = (x, y) => List.Alternate(Text.ToList(Text.From(x)), 1, 1, y),
C = Text.Combine,
D = List.Transform,
E = D(A, each C(D(List.Zip({List.Sort(B(_, 1), 0), B(_, 0)}), C)))
in
EPower Query solution 6 for Sort Odd Position Values, proposed by Tyler N.:
let
a = YourTable,
b = Table.AddColumn(
a,
"x",
each
let
c = [Numbers],
d = Text.Length(c) - 1,
e = List.Sort(List.Transform({0 .. Number.RoundDown(d / 2)}, each Text.At(c, _ * 2))),
f = List.Transform({0 .. d}, each if Number.IsOdd(_) then Text.At(c, _) else e{_ / 2})
in
Text.Combine(f)
)
in
bSolving the challenge of Sort Odd Position Values with Excel
Excel solution 1 for Sort Odd Position Values, proposed by Bo Rydobon 🇹🇭:
=MAP(A2:A10,
LAMBDA(n,
LET(s,
SEQUENCE(
LEN(
n
)
),
m,
MID(
n,
s,
1
),
o,
ISODD(
s
),
CONCAT(SORTBY(SORTBY(m,
o*(m+1)),
SORTBY(
s,
o
))))))Excel solution 2 for Sort Odd Position Values, proposed by Rick Rothstein:
=MAP(A2:A10,
LAMBDA(x,
LET(m,
MID(x,
SEQUENCE((LEN(
x
)+1)/2,
2),
1),
CONCAT(
HSTACK(
SORT(
TAKE(
m,
,
1
)
),
TAKE(
m,
,
-1
)
)
))))Excel solution 3 for Sort Odd Position Values, proposed by John V.:
=MAP(A2:A10,
LAMBDA(x,
LET(n,
MID(x,
SEQUENCE((1+LEN(
x
))/2,
2),
1),
CONCAT(
TAKE(
SORT(
n
),
,
1
)&DROP(
n,
,
1
)
))))Excel solution 4 for Sort Odd Position Values, proposed by Kris Jaganah:
=MAP(A2:A10,LAMBDA(y,LET(a,LAMBDA(x,MID(y,SEQUENCE(LEN(y)/2+0.5,,x,2),1)),CONCAT(HSTACK(SORT(a(1)),a(2))))))Excel solution 5 for Sort Odd Position Values, proposed by Timothée BLIOT:
=MAP(A2:A10,LAMBDA(z,LET(A,WRAPROWS(MID(z,SEQUENCE(LEN(z)),1),2), CONCAT(TOCOL(HSTACK(SORT(TAKE(A,,1)),TAKE(A,,-1)),3)))))Excel solution 6 for Sort Odd Position Values, proposed by LEONARD OCHEA 🇷🇴:
=MAP(A2:A10,LAMBDA(x,LET(I,INDEX,m,WRAPROWS(REGEXEXTRACT(x,".",1),2,""),CONCAT(HSTACK(SORT(I(m,,1)),I(m,,2))))))
Without HASTCK , idea Bo Rydobon 🇹🇭 , JvdV -
=MAP(A2:A10,LAMBDA(x,LET(I,INDEX,m,WRAPROWS(REGEXEXTRACT(x,".",1),2,""),CONCAT(SORT(I(m,,1))&I(m,,2)))))Excel solution 7 for Sort Odd Position Values, proposed by Hamidi Hamid:
=LET(r,MID((A2:A10),SEQUENCE(,13),1),x,MID(BYROW(CHOOSECOLS(r,SEQUENCE(,6,1,2)),LAMBDA(a,CONCAT(SORT(a,,1,1)))),SEQUENCE(,6,1,1),1),y,CHOOSECOLS(r,SEQUENCE(,5,2,2)),yy,CHOOSECOLS(r,SEQUENCE(,6,2,2)),xy,HSTACK(x,yy),zz,BYROW(CHOOSECOLS(xy,{1728394105116}),CONCAT),zz)Excel solution 8 for Sort Odd Position Values, proposed by Jaroslaw Kujawa:
=MAP(
A2:A10;
LAMBDA(
x ;
LET(
a ;
SEQUENCE(
LEN(
x
)
) ;
b ;
MID(
x ;
a ;
1
) ;
c ;
SORT(
FILTER(
HSTACK(
b ;
a
) ;
ISODD(
a
)
)
) ;
d ;
HSTACK(
TAKE(
c ;
;
1
) ;
SEQUENCE(
ROWS(
c
) ;
;
;
2
)
) ;
1*CONCAT(
IF(
ISODD(
a
) ;
XLOOKUP(
a ;
TAKE(
d ;
;
-1
) ;
TAKE(
d ;
;
1
)
) ;
b
)
)
)
)
)Excel solution 9 for Sort Odd Position Values, proposed by JvdV –:
=MAP(
A2:A10,
LAMBDA(
s,
LET(
f,
REGEXEXTRACT,
CONCAT(
SORT(
f(
s,
"(^|.)K.",
1
),
,
,
1
)&f(
s,
".K.?",
1
)
)
)
)
)Excel solution 10 for Sort Odd Position Values, proposed by Imam Hambali:
=BYROW(
A2:A10,
LAMBDA(x, LET(
a, x,
b, MID(a, SEQUENCE(, LEN(a)),1)*1,
c, SEQUENCE(, COLUMNS(b)),
d, SORT(FILTER(b, ISODD(c)),,1,1),
e, SEQUENCE(, COLUMNS(d),1,2),
CONCAT(IF(ISEVEN(c), b, XLOOKUP(c,e,d)))
)
)
)Excel solution 11 for Sort Odd Position Values, proposed by Abdelrahman Omer, MBA, PMP:
=MAP(A2:A10,LAMBDA(x,LET(a,WRAPROWS(MID(x,SEQUENCE(LEN(x)),1),2),CONCAT(IFERROR(HSTACK(SORT(TAKE(a,,1)),DROP(a,,1)),"")))))Excel solution 12 for Sort Odd Position Values, proposed by Anup Kumar:
=BYROW(A2:A10,LAMBDA(x,LET(
nos, x,
seq, SEQUENCE(LEN(nos),,1),
arr,MID(nos,seq,1),
s_odd, SORT(FILTER(arr,ISODD(seq))),
eve, FILTER(arr,ISEVEN(seq)),
CONCAT(TOCOL(HSTACK(s_odd,eve),3))
)))Excel solution 13 for Sort Odd Position Values, proposed by Francesco Bianchi 🇮🇹:
=MAP(
A2:A10,
LAMBDA(
r,
LET(
sq,
SEQUENCE(
LEN(
r
)
),
md,
MID(
r,
sq,
1
),
ev,
HSTACK(
FILTER(
sq,
ISEVEN(
sq
)
),
FILTER(
md,
ISEVEN(
sq
)
)
),
od,
HSTACK(
FILTER(
sq,
ISODD(
sq
)
),
SORT(
FILTER(
md,
ISODD(
sq
)
)
)
),
CONCAT(
TAKE(
SORT(
VSTACK(
ev,
od
)
),
,
-1
)
)
)
)
)Solving the challenge of Sort Odd Position Values with Python
Python solution 1 for Sort Odd Position Values, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "574 Sort Numbers in Odd Positions Only.xlsx"
input = pd.read_excel(path, usecols="A", nrows=10)
test = pd.read_excel(path, usecols="B", nrows=10, dtype=str)
def process_numbers(number):
number = list(str(number))
odd_indices = range(0, len(number), 2)
odd_numbers = sorted(int(number[i]) for i in odd_indices)
for i, idx in enumerate(odd_indices):
number[idx] = str(odd_numbers[i])
return ''.join(number)
input['Answer Expected'] = input.iloc[:, 0].apply(process_numbers)
print(test['Answer Expected'].equals(input['Answer Expected'])) # True
Solving the challenge of Sort Odd Position Values with Python in Excel
Python in Excel solution 1 for Sort Odd Position Values, proposed by Alejandro Campos:
def sort_odd_positions(number):
digits = list(str(number))
odd_position_digits = sorted([digits[i] for i in range(0, len(digits), 2)])
for i in range(0, len(digits), 2):
digits[i] = odd_position_digits.pop(0)
return ''.join(digits)
numbers = xl("A2:A10")[0]
df = pd.DataFrame({'Original': numbers, 'Sorted': [sort_odd_positions(number) for number in numbers]})
df
Python in Excel solution 2 for Sort Odd Position Values, proposed by Anshu Bantra:
df = xl("A1:A10", headers=True)
def sort_odd_indices(num: int) -> str:
num_dict = {key: value for key, value in enumerate(str(num),1)}
odd_keys = [key for key in num_dict if key % 2 != 0]
sorted_odd_values = sorted((num_dict[key] for key in odd_keys))
for key, value in zip(odd_keys, sorted_odd_values):
num_dict[key] = value
return ''.join(num_dict.values())
df['Answer Expected'] = df.iloc[:,0].apply(sort_odd_indices)
df
Solving the challenge of Sort Odd Position Values with R
R solution 1 for Sort Odd Position Values, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/574 Sort Numbers in Odd Positions Only.xlsx"
input = read_excel(path, range = "A1:A10")
test = read_excel(path, range = "B1:B10")
process_numbers = function(number) {
number = strsplit(as.character(number), "")[[1]]
odd = seq(1, len>h(number), by = 2)
number[odd] = sort(as.numeric(number[odd]))
paste(number, collapse = "")
}
result = input %>%
mutate(`Answer Expected` = map_chr(Numbers, process_numbers))
all.equal(result$`Answer Expected`, test$`Answer Expected`)
#> [1] TRUE
&&