Provide a formula to list down all valid EAN-13 numbers. EAN-13 numbers are 13 digits and last digit is checksum digit. To calculate checksum digit 1. Take first 12 digits 2. Sum all digits at odd positions (starting from left) 3. Sum all digits at even positions. Multiply by 3 the result of sum at even positions. 4. Sum the results of step 2 and 3. 5. Take the last digit i.e. unit digit of the answer of step 4. 6. If this is 0, then 0 is checksum otherwise 10 minus Result of step 5 is checksum. This checksum digit has to be equal to 13th digit for a valid EAN-13 number
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 91
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of List Valid EAN-13 Numbers with Power Query
Power Query solution 1 for List Valid EAN-13 Numbers, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
RS = Table.SelectRows(
Source,
each
let
t = List.Transform(Text.ToList([#"EAN-13 Numbers"]), Number.From)
in
Number.Mod(
10
- Number.Mod(
List.Sum(List.Transform(List.Split(List.RemoveLastN(t), 2), each _{0} + _{1} * 3)),
10
),
10
)
= List.Last(t)
)
in
RSPower Query solution 2 for List Valid EAN-13 Numbers, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Split = Table.TransformColumns(Source, {"EAN-13 Numbers", Splitter.SplitTextByRepeatedLengths(1)}),
TablaComp = Table.FromColumns(
{
List.Transform(
Split[#"EAN-13 Numbers"],
each
let
a = _,
b = List.Transform(_, Number.From),
c = List.RemoveLastN(b, 1),
d = List.Sum(List.Alternate(c, 1, 1)) * 3,
e = List.Sum(List.Alternate(c, 1, 1, 1)),
f = d + e,
g =
if Number.From(Text.End(Text.From(f), 1)) = 0 then
0
else
10 - Number.From(Text.End(Text.From(f), 1))
in
g
),
Source[#"EAN-13 Numbers"]
},
type table [Column1 = Text.Type, Column2 = Text.Type]
),
Solucion = Table.SelectRows(
Table.AddColumn(
TablaComp,
"Expected Answer",
each if Text.From([Column1]) = Text.End([Column2], 1) then [Column2] else null
)[[Expected Answer]],
each ([Expected Answer] <> null)
)
in
SolucionPower Query solution 4 for List Valid EAN-13 Numbers, proposed by Brian Julius:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Split = Table.SplitColumn(
Source,
"EAN-13 Numbers",
Splitter.SplitTextByPositions({0, 12}, false),
{"EAN-13 Numbers.1", "EAN-13 Numbers.2"}
),
LastDigit = Table.AddColumn(
Split,
"NumList",
each [
a = Text.ToList([#"EAN-13 Numbers.1"]),
b = List.Transform(a, Number.FromText),
SumOdd = List.Sum(List.Alternate(b, 1, 1, 1)),
SumEven3 = List.Sum(List.Alternate(b, 1, 1)) * 3,
TotalSum = SumEven3 + SumOdd,
LastDigit = List.Last(Text.ToList(Text.From(TotalSum))),
CheckSum = if Number.From(LastDigit) = 0 then 0 else 10 - Number.From(LastDigit)
][CheckSum]
),
Filter = Table.RemoveColumns(
Table.SelectRows(LastDigit, each Number.From([#"EAN-13 Numbers.2"]) = [NumList]),
"NumList"
),
Merged = Table.CombineColumns(
Filter,
{"EAN-13 Numbers.1", "EAN-13 Numbers.2"},
Combiner.CombineTextByDelimiter("", QuoteStyle.None),
"EAN-13 Numbers"
)
in
MergedPower Query solution 5 for List Valid EAN-13 Numbers, proposed by Matthias Friedmann:
let
Source = Excel.CurrentWorkbook(){[Name = "EAN"]}[Content],
#"Filtered Rows" = Table.SelectRows(
Source,
each [
list = List.Transform(Text.ToList([#"EAN-13 Numbers"]), Number.From),
odd = List.Sum(List.Transform(List.Select({0 .. 11}, each Number.IsEven(_)), each list{_})),
even = List.Sum(
List.Transform(List.Select({0 .. 11}, each not Number.IsEven(_)), each list{_})
),
last = Number.From(Text.End(Text.From(odd + even * 3), 1)),
check = ((if last = 0 then 0 else 10) - last = List.Last(list)) and List.Count(list) = 13
][check]
)
in
#"Filtered Rows"Power Query solution 6 for List Valid EAN-13 Numbers, proposed by Victor Wang:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = List.Select(
Source[#"EAN-13 Numbers"],
each [
a = List.Transform(Text.ToList(Text.Start(_, 12)), Number.From),
b = List.Sum(List.Alternate(a, 1, 1, 1)),
c = 3 * List.Sum(List.Alternate(a, 1, 1)),
d = b + c,
e = Number.From(Text.End(Text.From(d), 1)),
f = if e = 0 then 0 else 10 - e,
g = Text.End(_, 1) = Text.From(f)
][g]
)
in
ResultSolving the challenge of List Valid EAN-13 Numbers with Excel
Excel solution 1 for List Valid EAN-13 Numbers, proposed by Bo Rydobon 🇹🇭:
=LET(z,A2:A11,s,SEQUENCE(12),FILTER(z,MOD(z,10)=MOD(MMULT(-MID(z,TOROW(s),1),2*MOD(s-1,2)+1),10)))
Shorter but not practical.
=LET(z,A2:A11,s,ROW(1:12),FILTER(z,MOD(z,10)=MOD(MMULT(-MID(z,TOROW(s),1),2*MOD(s-1,2)+1),10)))Excel solution 2 for List Valid EAN-13 Numbers, proposed by Rick Rothstein:
=LET(
a,
A2:A11,
s,
SEQUENCE(
6,
,
,
2
),
FILTER(
a,
MAP(
a,
LAMBDA(
x,
MOD(
x,
10
)=MOD(
10-SUM(
MID(
x,
s,
1
)+3*MID(
x,
1+s,
1
)
),
10
)
)
)
)
)Excel solution 3 for List Valid EAN-13 Numbers, proposed by John V.:
=LET(e,A2:A11,FILTER(e,RIGHT(e)=MAP(e,LAMBDA(x,RIGHT(10-RIGHT(SUM(MID(x,2*ROW(1:6)-{1,0},1)*{1,3})))))))Excel solution 4 for List Valid EAN-13 Numbers, proposed by محمد حلمي:
=FILTER(
A2:A11,
MAP(
A2:A11,
LAMBDA(
a,
LET(
s,
SEQUENCE(
12
),
l,
MID(
LEFT(
a,
12
),
s,
1
)+0,
r,
RIGHT(
SUM(
IF(
ISODD(
s
),
l,
l*3
)
)
),
IF(
r+0,
10-r,
0
)
)=RIGHT(
a
)+0
)
)
)Excel solution 5 for List Valid EAN-13 Numbers, proposed by 🇰🇷 Taeyong Shin:
=LET(
Ean, A2:A11,
n, MAP( LEFT(Ean, 12), LAMBDA(m,
MOD(10 - SUM(BYCOL(--WRAPROWS(MID(m, SEQUENCE(12), 1), 2), LAMBDA(bc, SUM(bc))) * {1,3}), 10)
)),
FILTER(Ean, MOD(Ean, 10) = n)
)Excel solution 6 for List Valid EAN-13 Numbers, proposed by Kris Jaganah:
=LET(a,
A2:A11,
b,
BYROW(
a,
LAMBDA(
x,
SUM(
MID(
x,
SEQUENCE(
,
6,
1,
2
),
1
)/1
)
)
),
c,
BYROW(a,
LAMBDA(y,
(SUM(
MID(
y,
SEQUENCE(
,
6,
2,
2
),
1
)/1
)*3))),
d,
RIGHT((b+c),
1)/1,
e,
IF(
IF(
d=0,
0,
10-d
)=RIGHT(
a,
1
)/1,
a,
""
),
FILTER(
e,
e<>""
))Excel solution 7 for List Valid EAN-13 Numbers, proposed by Julian Poeltl:
=FILTER(
A2:A11,
MAP(
A2:A11,
LAMBDA(
N,
LET(
T,
LEFT(
N,
12
),
S,
SEQUENCE(
12
),
SP,
MID(
T,
S,
1
),
R,
--RIGHT(
SUM(
SP*ISODD(
S
)
)+SUM(
SP*ISEVEN(
S
)*3
),
1
),
IF(
R<>0,
10-R,
0
)=--RIGHT(
N,
1
)
)
)
)
)Excel solution 8 for List Valid EAN-13 Numbers, proposed by Alejandro Campos:
=LET(
result,
MAP(
A2:A11,
LAMBDA(
ean,
LET(
digits,
MID(
ean,
SEQUENCE(
1,
12
),
1
) + 0,
sum_odd,
SUM(
INDEX(
digits,
SEQUENCE(
1,
6,
1,
2
)
)
),
sum_even,
SUM(
INDEX(
digits,
SEQUENCE(
1,
6,
2,
2
)
)
) * 3,
total_sum,
sum_odd + sum_even,
checksum_digit,
MOD(
10 - MOD(
total_sum,
10
),
10
),
IF(
checksum_digit = RIGHT(
ean,
1
) + 0,
ean,
""
)
)
)
),
FILTER(
result,
result <> ""
)
)Excel solution 9 for List Valid EAN-13 Numbers, proposed by Aditya Kumar Darak 🇮🇳:
=LET(
_d,
A2:A11,
_e,
LAMBDA(
a,
LET(
s,
SEQUENCE(
6,
2
),
di,
MID(
a,
s,
1
),
t,
SUM(
di * {1,
3}
),
r,
MOD(
t,
10
) = MOD(
10 - RIGHT(
a
),
10
),
r
)
),
_c,
MAP(
_d,
_e
),
_r,
FILTER(
_d,
_c
),
_r
)Excel solution 10 for List Valid EAN-13 Numbers, proposed by Timothée BLIOT:
=LET(Data, A2:A11, Total, BYROW(Data, LAMBDA(a,
SUM(VALUE(INDEX(WRAPROWS(MID(LEFT(a,12),SEQUENCE(LEN(LEFT(a,12))),1),2),,1)))+SUM(VALUE(INDEX(WRAPROWS(MID(LEFT(a,12),SEQUENCE(LEN(LEFT(a,12))),1),2),,2)))*3 )), FILTER(Data,IF(IF(VALUE(RIGHT(Total))=0,0-VALUE(RIGHT(Data)),10-VALUE(RIGHT(Total))-VALUE(RIGHT(Data)))=0,1,0)))Excel solution 11 for List Valid EAN-13 Numbers, proposed by Bhavya Gupta:
=FILTER(A2:A11,MAP(A2:A11,LAMBDA(n,LET(s,SEQUENCE(12),m,MID(LEFT(n,12),s,1),r,RIGHT(SUM(ISODD(s)*m)+SUM(ISEVEN(s)*m)*3)*1,IF(r,10-r,0)=RIGHT(n)*1))))Excel solution 12 for List Valid EAN-13 Numbers, proposed by Stefan Olsson:
=LAMBDA(ean,
FILTER(ean,
BYROW(
QUERY({ArrayFormula(SPLIT(REGEXREPLACE(ean&"", "(.)","$1,"),","))},
"Select (Col1+Col3+Col5+Col7+Col9+Col11)+3*(Col2+Col4+Col6+Col8+Col10+Col12), Col13 Label (Col1+Col3+Col5+Col7+Col9+Col11)+3*(Col2+Col4+Col6+Col8+Col10+Col12) ''",0),
LAMBDA(rr,
AND(
IFS(
REGEXMATCH(INDEX(rr,1,1)&"","0$"),0,
TRUE,10-REGEXEXTRACT(INDEX(rr,1,1)&"","d$")
)=INDEX(rr,1,2)
)
)
)
)
)(A2:A11)Excel solution 13 for List Valid EAN-13 Numbers, proposed by Victor Momoh (MVP, MOS, R.Eng):
=FILTER(A2:A11,
MAP(A2:A11,
LAMBDA(x,
LET(a,
SEQUENCE(
6,
,
,
2
),
b,
SUM(
MID(
x,
a,
1
)+3*
MID(
x,
1+a,
1
)
),
c,
RIGHT(
b
),
0+RIGHT(
x
)=(10-c)*(1*c<>0)))))Excel solution 14 for List Valid EAN-13 Numbers, proposed by Artur Pilipczuk:
=LET(
z_,
A2:A11,
ev,
SEQUENCE(
6,
1,
2,
2
),
od,
SEQUENCE(
6,
1,
1,
2
),
sm,
LAMBDA(
lw,
nm,
MID(
nm,
lw,
1
)
),
sod,
MAP(
z_,
LAMBDA(
z,
& SUM(
MAP(
od,
LAMBDA(
odk,
SUM(
sm(
odk,
z
)*1
)
)
)
)
)
),
sev,
MAP(
z_,
LAMBDA(
z,
SUM(
MAP(
ev,
LAMBDA(
evk,
SUM(
sm(
evk,
z
)*1
)
)
)
)
)
),
ls,
RIGHT(
sev*3+sod,
1
),
wyn,
IF(
1*ls=0,
0,
10-ls
),
FILTER(
z_,
1*RIGHT(
z_,
1
)=wyn
)
)Solving the challenge of List Valid EAN-13 Numbers with Python
Python solution 1 for List Valid EAN-13 Numbers, proposed by Igor Perković:
import pandas as pd
# Source
df = pd.read_excel('Challenge_91.xlsx', sheet_name = 'Data')
# Processing
for n in df.values.tolist():
odd_sum = sum(int(o) for o in list(str(n[0])[0:-1:2]))
even_sum = sum(int(e) for e in list(str(n[0])[1::2]))*3
check_sum = int(str(odd_sum + even_sum)[-1])
checksum = 0
if check_sum:
checksum = 10-check_sum
# Result
if int(str(n[0])[-1]) == checksum:
print(n, checksum)
Python solution 2 for List Valid EAN-13 Numbers, proposed by Kenneth Uchenna:
import openpyxl
import mymodule
print(mymodule.checks(mymodule.new_lists, mymodule.new_list3))
mymodule.wb.save("Answered.xlsx")
Raphael Okoye
Solving the challenge of List Valid EAN-13 Numbers with Python in Excel
Python in Excel solution 1 for List Valid EAN-13 Numbers, proposed by Alejandro Campos:
def calculate_checksum(ean): return (10 - (sum(int(e) * (3 if i % 2 else 1) for i, e in enumerate(str(ean)[:12])) % 10)) % 10
df_valid_ean13 = pd.DataFrame([e for e in xl("A2:A11")[0] if calculate_checksum(e) == int(e[-1])], columns=["Valid EAN-13"])
Solving the challenge of List Valid EAN-13 Numbers with SQL
SQL solution 1 for List Valid EAN-13 Numbers, proposed by Zoran Milokanović:
WITH
DATA_PREPARATION
AS
(
SELECT
ROW_NUMBER() OVER () AS ORDINAL_NUMBER
,D.EAN_13_NUMBERS
FROM DATA D
)
SELECT
DP.EAN_13_NUMBERS AS EXPECTED_ANSWERS
FROM DATA_PREPARATION DP
WHERE
MOD(10 - MOD(/*ODD POSITIONS TOTAL*/
SUBSTRING(DP.EAN_13_NUMBERS, 1, 1) +
SUBSTRING(DP.EAN_13_NUMBERS, 3, 1) +
SUBSTRING(DP.EAN_13_NUMBERS, 5, 1) +
SUBSTRING(DP.EAN_13_NUMBERS, 7, 1) +
SUBSTRING(DP.EAN_13_NUMBERS, 9, 1) +
SUBSTRING(DP.EAN_13_NUMBERS, 11, 1)
+ 3 * /*EVEN POSITIONS TOTAL*/ (
SUBSTRING(DP.EAN_13_NUMBERS, 2, 1) +
SUBSTRING(DP.EAN_13_NUMBERS, 4, 1) +
SUBSTRING(DP.EAN_13_NUMBERS, 6, 1) +
SUBSTRING(DP.EAN_13_NUMBERS, 8, 1) +
SUBSTRING(DP.EAN_13_NUMBERS, 10, 1) +
SUBSTRING(DP.EAN_13_NUMBERS, 12, 1)), 10), 10) /*CHECKSUM DIGIT*/ =
SUBSTRING(DP.EAN_13_NUMBERS, 13, 1) /*LAST DIGIT*/
ORDER BY
DP.ORDINAL_NUMBER
;