IMO Number of a Vessel – This is a 7 digit number where last digit is check digit. Check digit is calculated by multiplying first 6 digits (left to right) from 7 to 2 respectively, sum them and taking the last digit of the result. Ex. 805353 = 8*7+0*6+5*5+3*4+5*3+3*2 = 114 = Last digit is 4. Hence, IMO number is 8053534. From the given IMO numbers, one digit is missing which is denoted by X. Work out the complete IMO numbers. In case of multiple answers, one answer is sufficient.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 414
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Validate IMO Check Digit with Power Query
Power Query solution 1 for Validate IMO Check Digit, proposed by Zoran Milokanović:
leting IMO numbers w/ hashtag#powerquery. hashtag#bitanbit hashtag#powerbi
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.TransformRows(Source, each List.Last(List.TransformMany(List.Transform({"0" .. "9"}, (t) => Text.Replace([IMO Number], "X", t)), (x) => let n = List.Transform({0 .. 6}, each Number.From(Text.At(x, _)) * (7 - _)) in {{}, {x}}{Number.From(Number.Mod(List.Sum(List.FirstN(n, 6)), 10) = n{6})}, (x, _) => Number.From(_))))
in
S
Power Query solution 2 for Validate IMO Check Digit, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content],
Sol = Table.AddColumn(
Origen,
"Answer",
(t) =>
let
a = Text.ToList(t[IMO Number]),
b = List.Last(a),
c = List.Reverse({2 .. 7}),
d =
if b = "X" then
Text.Replace(
t[IMO Number],
"X",
List.Last(
Text.ToList(
Text.From(
List.Sum(
List.Transform({0 .. List.Count(a) - 2}, each Number.From(a{_}) * c{_})
)
)
)
)
)
else
let
e = List.Transform(
{"0" .. "9"},
each {
_,
List.Last(
Text.ToList(
Text.From(
List.Sum(
List.Transform(
{0 .. 5},
(x) =>
Number.From(Text.ToList(Text.Replace(t[IMO Number], "X", _)){x})
* c{x}
)
)
)
)
)
}
),
f = Text.Replace(t[IMO Number], "X", List.Select(e, each _{1} = b){0}{0})
in
f
in
d
)
in
SolPower Query solution 3 for Validate IMO Check Digit, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
Part2:
T2 = Table.Group(J, {"IMO Number"}, {{"X", each List.First([L])}}),
K = Table.Combine({T1,T2}),
L = Table.TransformColumnTypes(K,{{"X", type text}, {"IMO Number", type text}}),
M = Table.AddColumn(L, "Answer", each Text.Replace([IMO Number],"X",[X])),
Sol = Table.SelectColumns(M,{"IMO Number", "Answer"})
in
Sol
Power Query solution 4 for Validate IMO Check Digit, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S= Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
A = Table.AddColumn(S, "X", each let
L1=List.ReplaceValue(Text.ToList(Text.Start([IMO Number],6)),"X","0",Replacer.ReplaceText),
L2= List.Numbers(7,6,-1),
LP = List.Positions(L1),
RS = Text.End(Text.From(List.Sum(List.Transform(LP, each Number.From( L1{_}) * L2{_} ))),1)
in
RS),
B = Table.AddColumn(A, "T", each Text.PositionOf([IMO Number],"X")=6),
T1 = Table.RemoveColumns(Table.SelectRows(B, each ([T] = true)),{"T"}),
T= Table.RemoveColumns(Table.SelectRows(B, each ([T] = false)),{"T"}),
D = Table.AddColumn(T, "Dif", each Number.Abs(Number.From(Text.End([IMO Number],1))-Number.From([X]))),
C = Table.AddColumn(D, "X@", each 7-Number.From(Text.PositionOf([IMO Number],"X")),Int64.Type),
F = Table.AddColumn(C, "L", each {0..9}),
E = Table.ExpandListColumn(F, "L"),
H = Table.AddColumn(E, "Custom", each Text.End( Text.From([#"X@"]*[L]),1)=Text.From([Dif])),
I = Table.SelectRows(H, each ([Custom] = true)),
J = Table.SelectColumns(I,{"IMO Number", "L"}),
Solving the challenge of Validate IMO Check Digit with Excel
Excel solution 1 for Validate IMO Check Digit, proposed by Bo Rydobon 🇹🇭:
=MAP(A2:A10,
LAMBDA(a,
ARRAYTOTEXT(TOCOL(MAP(SUBSTITUTE(
a,
"X",
SEQUENCE(
10,
,
0
)
),
LAMBDA(v,
LET(s,
SEQUENCE(
6
),
v/(RIGHT(SUM(MID(
v,
s,
1
)*(8-s)))=RIGHT(
v
))))),
3))))Excel solution 2 for Validate IMO Check Digit, proposed by Rick Rothstein:
=LET(
d,
765432,
x,
FIND(
"X",
A2
),
s,
SUM(
MID(
REPLACE(
d,
x,
1,
0
),
SEQUENCE(
,
6
),
1
)*MID(
SUBSTITUTE(
A2,
"X",
1
),
SEQUENCE(
,
LEN(
A2
)-1
),
1
)
),
n,
SEQUENCE(
10,
,
0
),
IF(
RIGHT(
A2
)="X",
REPLACE(
A2,
7,
1,
RIGHT(
s
)
),
REPLACE(
A2,
x,
1,
FILTER(
n,
RIGHT(
s+MID(
d,
x,
1
)*n
)=RIGHT(
A2
)
)
)
)
)Excel solution 3 for Validate IMO Check Digit, proposed by John V.:
=MAP(A2:A10,
LAMBDA(n,
@TOCOL(MAP(SUBSTITUTE(
n,
"X",
ROW(
1:10
)-1
),
LAMBDA(x,
x/(RIGHT(SUM(MID(
x,
ROW(
1:6
),
1
)*(8-ROW(
1:6
))))=RIGHT(
x
)))),
2)))Excel solution 4 for Validate IMO Check Digit, proposed by محمد حلمي:
=MAP(
A2:A10,
LAMBDA(
a,
LET(
s,
SEQUENCE(
6
),
j,
8-s,
m,
MID(
a,
s,
1
),
i,
SEQUENCE(
10
)-1,
--SUBSTITUTE(
a,
"X",
XLOOKUP(
TRUE,
RIGHT(
SUM(
TOCOL(
m*j,
2
)
)+i*XLOOKUP(
"x",
m,
j
)
)=RIGHT(
a
),
i,
RIGHT(
SUM(
j*m
)
)
)
)
)
)
)Excel solution 5 for Validate IMO Check Digit, proposed by Julian Poeltl:
=MAP(
A2:A10,
LAMBDA(
I,
LET(
SP,
MID(
I,
SEQUENCE(
1,
7
),
1
),
First,
DROP(
SP,
,
-1
),
X,
IF(
CHOOSECOLS(
SP,
7
)<>"X",
XMATCH(
TAKE(
SP,
,
-1
),
RIGHT(
BYROW(
IFERROR(
First*SEQUENCE(
1,
6,
7,
-1
),
SEQUENCE(
10,
,
0
)*SEQUENCE(
1,
6,
7,
-1
)
),
LAMBDA(
A,
SUM(
A
)
)
)
)
)-1,
RIGHT(
SUM(
First*SEQUENCE(
1,
6,
7,
-1
)
),
1
)
),
CONCAT(
IF(
SP="X",
X,
SP
)
)
)*1
)
)Excel solution 6 for Validate IMO Check Digit, proposed by Timothée BLIOT:
=MAP(A2:A10,
LAMBDA(z,
LET(A,
MAP(
ROW(
$1:$10
)-1,
LAMBDA(
x,
SUBSTITUTE(
z,
"X",
x
)
)
),
ARRAYTOTEXT(FILTER(A,
MAP(A,
LAMBDA(y,
RIGHT(SUM(MAP(ROW(
$1:$6
),
LAMBDA(x,
MID(
y,
x,
1
)*(8-x)))))=RIGHT(
y
))))))))Excel solution 7 for Validate IMO Check Digit, proposed by Sunny Baggu:
=MAP(
A2:A10,
LAMBDA(
a,
LET(
_m,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
_s7,
SEQUENCE(
7,
,
7,
-1
),
_p,
_m * _s7,
_fp,
DROP(
_p,
-1
),
_s,
SUM(
IFERROR(
_fp,
0
)
),
_n,
FILTER(
_s7,
ISERR(
_p
)
),
_s9,
SEQUENCE(
10
) - 1,
_sp,
_n * _s9,
_c1,
_s + _sp,
_c2,
IF(
RIGHT(
_c1
) = TAKE(
_m,
-1
),
_s9,
x
),
_c3,
IFERROR(
TAKE(
TOCOL(
_c2,
3
),
1
),
RIGHT(
SUM(
DROP(
_p,
-1
)
)
)
),
SUBSTITUTE(
a,
"X",
_c3
)
)
)
)Excel solution 8 for Validate IMO Check Digit, proposed by LEONARD OCHEA 🇷🇴:
=MAP(A2:A10,
LAMBDA(a,
LET(s,
SEQUENCE(
,
10,
0
),
e,
MID(
SUBSTITUTE(
a,
"X",
s
),
SEQUENCE(
7
),
1
),
ARRAYTOTEXT(SUBSTITUTE(a,
"X",
TOCOL(s/(RIGHT(
MMULT(
SEQUENCE(
,
6,
7,
-1
),
--TAKE(
e,
6
)
)
)=RIGHT(
TAKE(
e,
-1
)
)),
3))))))
Only one solution,
with recursive function
=LET(R,
LAMBDA(R,
x,
y,
LET(n,
SUBSTITUTE(
x,
"X",
y
),
s,
SEQUENCE(
6
),
IF(RIGHT(SUM(MID(
n,
s,
1
)*(8-s)))=RIGHT(
n
),
n,
R(
R,
x,
y+1
)))),
MAP(
A2:A10,
LAMBDA(
a,
R(
R,
a,
0
)
)
))Excel solution 9 for Validate IMO Check Digit, proposed by Abdallah Ally:
=MAP(
A2:A10,
LAMBDA(
x,
ARRAYTOTEXT(
DROP(
LET(
a,
x,
REDUCE(
"",
SEQUENCE(
10,
,
0
),
LAMBDA(
x,
y,
LET(
b,
SUBSTITUTE(
a,
"X",
y
),
c,
MID(
LEFT(
b,
6
),
SEQUENCE(
6
),
1
)*SEQUENCE(
6,
,
7,
-1
),
IF(
RIGHT(
SUM(
c
)
)=RIGHT(
b
),
HSTACK(
x,
b
),
x
)
)
)
)
),
& ,
1
)
)
)
)Excel solution 10 for Validate IMO Check Digit, proposed by Pieter de B.:
=MAP(
A2:A10,
LAMBDA(
v,
REDUCE(
v,
SEQUENCE(
10,
,
0
),
LAMBDA(
x,
y,
LET(
z,
SUBSTITUTE(
x,
"X",
y
),
IF(
RIGHT(
SUM(
MID(
z,
SEQUENCE(
6
),
1
)*SEQUENCE(
6,
,
7,
-1
)
)
)=RIGHT(
z
),
--z,
x
)
)
)
)
)
)
Or
=MAP(A2:A10,
LAMBDA(v,
REDUCE(v,
SEQUENCE(
10,
,
0
),
LAMBDA(x,
y,
LET(s,
SEQUENCE(
6
),
z,
SUBSTITUTE(
x,
"X",
y
),
IF(RIGHT(SUM(MID(
z,
s,
1
)*(8-s)))=RIGHT(
z
),
--z,
x))))))
Or inspired by Charles Roldan :
=MAP(
A2:A10,
LAMBDA(
x,
LET(
y,
SUBSTITUTE(
x,
"X",
ROW(
1:10
)-1
),
--INDEX(
y,
XMATCH(
0,
MOD(
MMULT(
--MID(
y,
COLUMN(
A:G
),
1
),
{7;6;5;4;3;2;-1}
),
10
)
)
)
)
)
)Excel solution 11 for Validate IMO Check Digit, proposed by Charles Roldan:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
y,
SUBSTITUTE(
x,
"X",
{0;1;2;3;4;5;6;7;8;9}
),
--INDEX(
y,
XMATCH(
0,
MOD(
MMULT(
--MID(
y,
{1,
2,
3,
4,
5,
6,
7},
1
),
{7;6;5;4;3;2;-1}
),
10
)
)
)
)
)
)Excel solution 12 for Validate IMO Check Digit, proposed by Charles Roldan:
=MAP(
A2:A10, LAMBDA(
x, LET(
y, SUBSTITUTE(
x, "X", {0;
1;
2;
3;
4;
5;
6;
7;
8;
9}
), ARRAYTOTEXT(
FILTER(
y, 0 = MOD(
MMULT(
--MID(
y, {1,2,3,4,5,6,7}, 1
), {7;
6;
5;
4;
3;
2;
-1}
), 10
)
)
)
)
)
)Excel solution 13 for Validate IMO Check Digit, proposed by Giorgi Goderdzishvili:
= pd.read_clipboard(usecols = [0], names = ["IMO Number"], header=0)
tst = df["IMO Number"].to_list()
fin = []
for i in tst:
# Last Check
if i[-1] == "X":
eq = [int(k)*(7-n) for n,k in enumerate(i[:-1])]
X = str(sum(eq))[-1]
# Other Cases
else:
x_pos = i.find("X")
other_sm = sum( [int(k)*(7-n) for n,k in enumerate(i[:-1]) if n!=x_pos] )
for nm in range(10):
if str((other_sm + nm*(7-x_pos)))[-1] == i[-1]:
X = str(nm)
fin.append(i.replace("X",X))Excel solution 14 for Validate IMO Check Digit, proposed by LUIS FLORENTINO COUTO CORTEGOSO:
=MAP(
A2:A10,
LAMBDA(
q,
LET(
f,
RIGHT(
q
),
w,
LEFT(
REPLACE(
q,
SEARCH(
"X",
q
),
1,
SEQUENCE(
10,
,
0,
1
)
),
6
),
s,
SEQUENCE(
6,
,
7,
-1
),
e,
SORT(
s
)-1,
n,
BYROW(
w,
LAMBDA(
x,
LET(
d,
MID(
x,
e,
1
),
SUMPRODUCT(
s,
--d
)
)
)
),
--IFERROR(
@FILTER(
w,
RIGHT(
n
)=f
)&f,
REPLACE(
A2,
7,
1,
RIGHT(
@n
)
)
)
)
)
)Excel solution 15 for Validate IMO Check Digit, proposed by Alexandra Popoff:
= lambda(
z_Input,
LET(
z_Factor,
SEQUENCE(
6,
1,
7,
-1
),
z_Seq,
SEQUENCE(
7,
1,
1,
1
),
z_Input_Char,
BYROW(
z_Seq,
LAMBDA(
z_i,
IFERROR(
VALUE(
RIGHT(
LEFT(
z_Input,
z_i
),
1
)
),
"X"
)
)
),
z_X_Row,
FILTER(
z_Seq,
ISNUMBER(
z_Input_Char
) = FALSE
),
z_Number,
DROP(
z_Input_Char,
-1
),
z_Sum,
SUM(
IFERROR(
z_Number * z_Factor,
0
)
),
z_Possible,
IF(
z_X_Row = 7,
RIGHT(
TEXT(
SUMPRODUCT(
z_Number,
z_Factor
),
"0"
),
1
),
FILTER(
SEQUENCE(
10,
1,
0,
1
),
RIGHT(
TEXT(
SEQUENCE(
10,
1,
0,
1
) * INDEX(
z_Factor,
z_X_Row,
1
) + z_Sum,
"0"
),
1
) = RIGHT(
z_Input,
1
)
)
),
TEXTJOIN(
"||",
TRUE,
SUBSTITUTE(
z_Input,
"X",
z_Possible
)
)
)
)Solving the challenge of Validate IMO Check Digit with Python in Excel
Python in Excel solution 1 for Validate IMO Check Digit, proposed by Abdallah Ally:
import pandas as pd
file_path = 'Excel_Challenge_414 - IMO Number of a Vessel.xlsx'
df = pd.read_excel(file_path, usecols='A:B')
def complete_imo_number(number):
values = []
sequence = range(7,1,-1)
for i in range(10):
new_number = number.replace('X', str(i))
total = 0
for j in range(6):
total += int(new_number[j]) * sequence[j]
if str(total)[-1] == new_number[-1]:
values.append(new_number)
return ', '.join(values)
df['My Answer'] = df['IMO Number'].apply(complete_imo_number)
print(f'n{df}')
&&