Convert every digit from right to left in the base of its index + 1 reading index 1, 2… from right to left (As there is no concept of base 1, hence we need to start with base 2, hence index + 1). Hence, right most digit will be converted into base 2, then 2nd right most digit will be converted into base 3 and so on. Sum the result thus obtained. Sort the numbers in ascending order on the basis of sum. Ex. 479 => 4 in base 4 + 7 in base 3 + 9 in base 2 = 10 + 31 + 1001 = 1032 3556 => 3 in base 5 + 5 in base 4 + 5 in base 3 + 6 in base 2 = 3 + 11 + 12 + 110 = 136 Hence, when we sort 3556 will come first and 479 will come after.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 527
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Index-wise Base Encoding Sort with Power Query
Power Query solution 1 for Index-wise Base Encoding Sort, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Calc = Table.AddColumn(
Source,
"Answer",
each
let
a = Text.From([Number]),
b = List.Reverse(List.Transform({1 .. Text.Length(a)}, each _ + 1)),
c = List.Transform(Text.ToList(a), Number.From),
d = List.Zip({c, b}),
Fx = (F, G, H) =>
let
A = Number.IntegerDivide(F, G),
B = Number.Mod(F, G),
C = H,
D = if F = 0 then List.Reverse(List.RemoveLastN(C & {A})) else @Fx(A, G, C & {B})
in
D,
Cal = List.Sum(
List.Transform(
d,
each Number.From(Text.Combine(List.Transform(Fx(_{0}, _{1}, {}), Text.From)))
)
)
in
Cal
),
Sol = Table.Sort(Calc, {{"Answer", 0}, {"Number", 0}})[[Number]]
in
SolSolving the challenge of Index-wise Base Encoding Sort with Excel
Excel solution 1 for Index-wise Base Encoding Sort, proposed by Bo Rydobon 🇹🇭:
=LET(
n,
A2:A10,
s,
SEQUENCE(
,
15,
0
),
SORTBY(
n,
BYROW(
--BASE(
RIGHT(
INT(
n/10^s
)
),
s+2
),
SUM
)
)
)Excel solution 2 for Index-wise Base Encoding Sort, proposed by Bo Rydobon 🇹🇭:
=SORTBY(A2:A10,MAP(A2:A10,LAMBDA(n,LET(s,SEQUENCE(15),SUM(--BASE(MID(n+10^14,s,1),17-s))))))Excel solution 3 for Index-wise Base Encoding Sort, proposed by Rick Rothstein:
=SORTBY(
A2:A10,
MAP(
A2:A10,
LAMBDA(
x,
SUM(
0+BASE(
MID(
x,
SEQUENCE(
LEN(
x
),
,
LEN(
x
),
-1
),
1
),
SEQUENCE(
LEN(
x
),
,
2
)
)
)
)
)
)Excel solution 4 for Index-wise Base Encoding Sort, proposed by John V.:
=SORTBY(
A2:A10,
MAP(
A2:A10,
LAMBDA(
x,
LET(
i,
LEN(
x
),
s,
SEQUENCE(
i
),
SUM(
--BASE(
MID(
x,
s,
1
),
2+i-s
)
)
)
)
)
)Excel solution 5 for Index-wise Base Encoding Sort, proposed by محمد حلمي:
=SORTBY(
A2:A10,
MAP(
A2:A10,
LAMBDA(
a,
LET(
i,
LEN(
a
),
s,
SEQUENCE(
i
),
SUM(
--BASE(
MID(
a,
s,
1
),
i-s+2
)
)
)
)
)
)Excel solution 6 for Index-wise Base Encoding Sort, proposed by Kris Jaganah:
=SORTBY(
A2:A10,
MAP(
A2:A10,
LAMBDA(
x,
LET(
a,
LEN(
x
),
b,
SEQUENCE(
a
),
c,
MID(
x,
b,
1
),
d,
a+2-b,
SUM(
--BASE(
c,
d
)
)
)
)
)
)Excel solution 7 for Index-wise Base Encoding Sort, proposed by Julian Poeltl:
=LET(A,A2:A10,SORTBY(A,MAP(A,LAMBDA(A,LET(L,LEN(A),S,SEQUENCE(L),SR,SEQUENCE(L,,L,-1),SUM(--BASE(MID(A,S,1),SR+1)))))))Excel solution 8 for Index-wise Base Encoding Sort, proposed by Aditya Kumar Darak 🇮🇳:
=SORTBY(
A2:A10,
MAP(
A2:A10,
LAMBDA(
a,
LET(
l,
LEN(
a
),
s,
SEQUENCE(
l,
,
1
),
b,
BASE(
MID(
a,
s,
1
),
l - s + 2
),
r,
SUM(
--b
),
r
)
)
)
)Excel solution 9 for Index-wise Base Encoding Sort, proposed by Timothée BLIOT:
=SORTBY(A2:A10,MAP(A2:A10,LAMBDA(z,LET(A,LEN(z),B,SEQUENCE(A),SUM(--MAP(B,LAMBDA(y,BASE(--MID(z,y,1),A+2-y))))))))Excel solution 10 for Index-wise Base Encoding Sort, proposed by Hussein SATOUR:
=SORTBY(
A2:A10,
MAP(
A2:A10,
LAMBDA(
x,
LET(
a,
LEN(
x
),
b,
SEQUENCE(
a
),
SUM(
--BASE(
MID(
x,
b,
1
),
a+2-b
)
)
)
)
)
)Excel solution 11 for Index-wise Base Encoding Sort, proposed by Oscar Mendez Roca Farell:
=SORTBY(
A2:A10,
-MAP(
A2:A10,
LAMBDA(
a,
LET(
s,
SEQUENCE(
LEN(
a
)
),
SUM(
-BASE(
MID(
a,
s,
1
),
MAX(
s
)-s+2
)
)
)
)
)
)Excel solution 12 for Index-wise Base Encoding Sort, proposed by Sunny Baggu:
=LET(
t,
A2:A10,
SORTBY(
t,
MAP(
t,
LAMBDA(
x,
LET(
l,
SEQUENCE(
LEN(
x
)
),
_m,
MID(
x,
l,
1
),
_n,
LEN(
x
) + 2 - l,
_b,
BASE(
_m,
_n
),
SUM(
_b + 0
)
)
)
)
)
)Excel solution 13 for Index-wise Base Encoding Sort, proposed by Anshu Bantra:
=LET( arr_, A2:A10,
base_, MAP( arr_,
LAMBDA(x,
LET(
num_,MID(x,SEQUENCE(LEN(x)),1)*1,
bas_,SORT(SEQUENCE(LEN(x),,2,1),,-1),
SUM(BASE(num_,bas_)*1)
))),
SORTBY(arr_,base_))Excel solution 14 for Index-wise Base Encoding Sort, proposed by ferhat CK:
=SORTBY(A2:A10,SCAN(0,A2:A10,LAMBDA(n,i,LET(a,--MID(i,SEQUENCE(LEN(i)),1),b,SEQUENCE(COUNTA(a),,COUNTA(a)+1,-1),SUM(--BASE(a,b))))))Excel solution 15 for Index-wise Base Encoding Sort, proposed by Jaroslaw Kujawa:
=LET( z ; A2:A10 ;
x ; HSTACK( z ;
BYROW( z ; LAMBDA( y ; LET( seq ; SEQUENCE( ; LEN(y)); SUM(--BASE(MID( y; seq; 1); LEN(y)+2-seq))))));
TAKE(SORT( x ; 2 ; 1) ; ; 1))Excel solution 16 for Index-wise Base Encoding Sort, proposed by Andy Heybruch:
=SORTBY(
A2:A10,
BYROW(
A2:A10,
LAMBDA(
_n,
LET(
_l,
LEN(
_n
),
_nums,
MID(
_n,
SEQUENCE(
_l
),
1
),
SUM(
--BASE(
_nums,
SEQUENCE(
_l,
,
_l+1,
-1
)
)
)
)
)
)
)Excel solution 17 for Index-wise Base Encoding Sort, proposed by Imam Hambali:
=SORTBY(
A2:A10,
DROP(
REDUCE(
"",
A2:A10,
LAMBDA(
x,
y,
VSTACK(
x,
SUM(
--BASE(
MID(
y,
SEQUENCE(
,
LEN(
y
)
),
1
),
SEQUENCE(
,
LEN(
y
),
LEN(
y
)+1,
-1
)
)
)
)
)
),
1
)
)Excel solution 18 for Index-wise Base Encoding Sort, proposed by Pieter de Bruijn:
=LET(n,A2:A10,l,LEN(n),s,SEQUENCE(,MAX(l)),SORTBY(n,MMULT(IFERROR(--BASE(MID(n,s,1),l-s+2),0),TOCOL(s^0))))
or hard entering the max length being 11:
=LET(n,A2:A10,s,SEQUENCE(,11),SORTBY(n,MMULT(IFERROR(--BASE(MID(n,s,1),LEN(n)-s+2),0),TOCOL(s^0))))Excel solution 19 for Index-wise Base Encoding Sort, proposed by El Badlis Mohd Marzudin:
=SORTBY(
A2:A10,
MAP(
A2:A10,
LAMBDA(
x,
LET(
a,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
b,
ROWS(
a
)+1,
c,
SEQUENCE(
b-1,
,
b,
-1
),
SUM(
BASE(
a,
c
)+0
)
)
)
)
)Excel solution 20 for Index-wise Base Encoding Sort, proposed by Ricardo Alexis Domínguez Hernández:
=SORTBY(
A2:A10,
BYROW(
A2:A10,
LAMBDA(
x,
LET(
a,
LEN(
x
),
SUM(
BASE(
MID(
x,
SEQUENCE(
,
a
),
1
),
SEQUENCE(
,
& a,
a+1,
-1
)
)*1
)
)
)
)
)Excel solution 21 for Index-wise Base Encoding Sort, proposed by Bevon Clarke:
=SORTBY(A2:A10,SORT(MAP(A2:A10,LAMBDA(x,LET(m,(--MID(x,SEQUENCE(,LEN(x)),1)),
s,SEQUENCE(,LEN(x),LEN(x),-1)+1,
b,(--BASE(m,s)),SUM(b))))))Solving the challenge of Index-wise Base Encoding Sort with Python
Python solution 1 for Index-wise Base Encoding Sort, proposed by Konrad Gryczan, PhD:
I used numpy.base_repr to convert decimals to another bases.
import pandas as pd
import numpy as np
path = "527 Sum of Digits in Different Bases.xlsx"
input = pd.read_excel(path, usecols = "A")
test = pd.read_excel(path, usecols = "B")
def convert_to_sum(number):
digits = [int(d) for d in str(number)]
rows = np.arange(len(digits), 0, -1) + 1
digits = [int(np.base_repr(d, base = r)) for d, r in zip(digits, rows)]
return sum(digits)
result = input.assign(Sum=input["Number"]
.apply(convert_to_sum))
.sort_values(by="Sum")
.drop("Sum", axis=1)
.reset_index(drop=True)
print(result["Number"].equals(test["Answer Expected"])) # True
Solving the challenge of Index-wise Base Encoding Sort with Python in Excel
Python in Excel solution 1 for Index-wise Base Encoding Sort, proposed by Alejandro Campos:
def convert_to_base(n, base):
return "0" if n == 0 else convert_to_base(n // base, base) + str(n % base)
def sum_converted_numbers(num_str):
return sum(int(convert_to_base(int(digit), i + 2)) for i, digit in enumerate(reversed(num_str)))
df = xl("A1:A10", headers=True).astype(str)
df['Converted Sum'] = df['Number'].apply(sum_converted_numbers)
df_sorted = df.sort_values(['Converted Sum', 'Number'], ascending=[True, False]).reset_index(drop=True)
df_sorted
Python in Excel solution 2 for Index-wise Base Encoding Sort, proposed by Anshu Bantra:
def convert_digit_to_base(digit, base):
if digit == 0:
return 0
digits = []
while digit:
digits.append(int(digit % base))
digit //= base
return int(''.join(str(x) for x in digits[::-1]))
def convert_number_to_bases(num):
digits = convert_to_digits(num)
bases = get_base_lst(digits)
converted_digits = [convert_digit_to_base(d, b)*1 for d, b in zip(digits, bases)]
return sum(converted_digits)
def convert_to_digits(num):
return [int(digit) for digit in str(num)]
def get_base_lst(lst):
return sorted(list(np.array(range(len(lst)))+2), reverse=True)
df = xl("A1:A10", headers=True)
df['Sorter'] = df['Number'].apply(lambda x: convert_number_to_bases(x))
df.sort_values(by='Sorter', ascending=True, inplace=True)
df['Number'].values
Solving the challenge of Index-wise Base Encoding Sort with R
R solution 1 for Index-wise Base Encoding Sort, proposed by Konrad Gryczan, PhD:
I found baseConvert function from Gmisc package that helped a lot.
library(tidyverse)
library(readxl)
library(Gmisc)
path = "Excel/527 Sum of Digits in Different Bases.xlsx"
input = read_excel(path, range = "A1:A10")
test = read_excel(path, range = "B1:B10")
convert_to_sum <- function(number) {
digits <- as.numeric(strsplit(as.character(number), "")[[1]]) %>%
tibble(num = .) %>%
mutate(row = nrow(.) + 2 - row_number()) %>%
rowwise() %>%
mutate(converted = Gmisc::baseConvert(num, target = row, base = 10) %>% as.numeric()) %>%
ungroup() %>%
summarise(sum = sum(converted)) %>%
pull()
return(digits)
}
result = input %>%
mutate(conv = map_dbl(Number, convert_to_sum)) %>%
arrange(conv) %>%
select(`Answer Expected` = Number)
identical(result, test)
#> [1] TRUE
&&