List those numbers only where odd digits appear odd number of times and even digits appear even number of times. Ex. 70505500 – odd digits are 7 and 5 and they appear 1 and 3 times, hence valid for odd digits. 0 appears 4 times, hence valid for even digits. Hence, this number is a valid answer. 12332 – 3 appears 2 times which makes this number invalid.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 266
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Check Digit Occurrence Validity with Power Query
Power Query solution 1 for Check Digit Occurrence Validity, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.SelectRows(
Source,
each List.AllTrue(
List.Transform(
List.Distinct(Text.ToList([Number])),
(n) => Number.IsEven(Text.Length(Text.Select([Number], n)) + Number.From(n))
)
)
)
in
AnsPower Query solution 2 for Check Digit Occurrence Validity, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content][Number],
S = List.Select(
Source,
each List.Accumulate(
{0 .. Text.Length(_) - 1},
true,
(s, c) =>
s
and (
let
t = Text.At(_, c)
in
Number.IsOdd(Number.From(t)) = Number.IsOdd(Text.Length(Text.Select(_, t)))
)
)
)
in
SPower Query solution 3 for Check Digit Occurrence Validity, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.SelectRows(
Source,
each
let
a = List.Transform(Text.ToList([Number]), Number.From),
b = List.Transform(List.Select(a, Number.IsEven), each Text.From(_)),
c = List.Transform(
List.Distinct(b),
each Number.IsEven(Text.Length(Text.Select(Text.Combine(b), _)))
),
d = List.Transform(List.Select(a, Number.IsOdd), each Text.From(_)),
e = List.Transform(
List.Distinct(d),
each Number.IsOdd(Text.Length(Text.Select(Text.Combine(d), _)))
)
in
List.AllTrue(e) and List.AllTrue(c)
)
in
SolPower Query solution 4 for Check Digit Occurrence Validity, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
res = Table.SelectRows(
Fonte,
each List.AllTrue(
Table.AddColumn(
Table.Group(
Table.FromColumns({Text.ToList([Number])}),
{"Column1"},
{{"Count", each Table.RowCount(_)}}
),
"val",
each
if (Number.IsOdd(Number.From([Column1])) and Number.IsOdd(Number.From([Count])))
or (Number.IsEven(Number.From([Column1])) and Number.IsEven(Number.From([Count])))
then
true
else
false
)[val]
)
= true
)
in
resSolving the challenge of Check Digit Occurrence Validity with Excel
Excel solution 1 for Check Digit Occurrence Validity, proposed by Bo Rydobon 🇹🇭:
=LET(
a,
A2:A10,
n,
SEQUENCE(
,
10,
0
),
c,
LEN(
a
)-LEN(
SUBSTITUTE(
a,
n,
)
),
FILTER(
a,
MMULT(
1-IF(
c,
MOD(
c,
2
)<>MOD(
n,
2
)
),
TOCOL(
n+1
)
)=55
)
)Excel solution 2 for Check Digit Occurrence Validity, proposed by Bo Rydobon 🇹🇭:
=LET(
a,
A2:A10,
n,
SEQUENCE(
,
10,
0
),
c,
LEN(
a
)-LEN(
SUBSTITUTE(
a,
n,
)
),
FILTER(
a,
MMULT(
MOD(
IF(
c,
c+n
),
2
),
TOCOL(
n+1
)
)=0
)
)Excel solution 3 for Check Digit Occurrence Validity, proposed by Rick Rothstein:
=FILTER(A2:A10,
MAP(A2:A10,
LAMBDA(c,
LET(f,
LAMBDA(n,
d,
LET(l,
LEN(
n
)-LEN(
SUBSTITUTE(
n,
d,
""
)
),
IF((ISODD(
d
)*ISODD(
l
))+(ISEVEN(
d
)*ISEVEN(
l
)),
1,
0))),
r,
REDUCE(
"",
MID(
c,
SEQUENCE(
LEN(
c
)
),
1
),
LAMBDA(
a,
x,
a&f(
c,
x
)
)
),
ISERR(
FIND(
0,
r
)
)))))Excel solution 4 for Check Digit Occurrence Validity, proposed by John V.:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
x,
LET(
b,
LEN(
x
)-LEN(
SUBSTITUTE(
x,
SEQUENCE(
5,
2
)-1,
)
),
AND(
IF(
b,
MOD(
b,
2
)={0,
1},
1
)
)
)
)
)
)Excel solution 5 for Check Digit Occurrence Validity, proposed by محمد حلمي:
= even number
even number -+ even number = even number
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
a,
LET(
x,
-MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
i,
FREQUENCY(
x,
x
),
AND(
IF(
i,
ISEVEN(
x-i
),
1
)
)
)
)
)
)Excel solution 6 for Check Digit Occurrence Validity, proposed by Kris Jaganah:
=TOCOL(MAP(A2:A10,
LAMBDA(y,
LET(a,
MID(
y,
SEQUENCE(
LEN(
y
)
),
1
),
b,
UNIQUE(
a
),
c,
MAP(b,
LAMBDA(x,
SUM(--(a=x)))),
IFS(
SUM(
MOD(
MOD(
b,
2
)+MOD(
c,
2
),
2
)
)=0,
y
)))),
3)Excel solution 7 for Check Digit Occurrence Validity, proposed by Timothée BLIOT:
=FILTER(A2:A10,
MAP(A2:A10,
LAMBDA(z,
LET(A,
MID(
z,
SEQUENCE(
LEN(
z
)
),
1
),
SUM(MAP(A,
LAMBDA(x,
--(MOD(
x,
2
)=MOD(SUM(--(x=A)),
2)))))=LEN(
z
)))))Excel solution 8 for Check Digit Occurrence Validity, proposed by Hussein SATOUR:
=LET(
n,
A2:A10,
FILTER(
n,
MAP(
n,
LAMBDA(
x,
LET(
a,
LEN(
x
),
b,
MID(
x,
SEQUENCE(
a
),
1
),
c,
a - LEN(
SUBSTITUTE(
x,
b,
""
)
),
COUNT(
UNIQUE(
ISEVEN(
b+c
)*1
)
)
)
)
)=1
)
)Excel solution 9 for Check Digit Occurrence Validity, proposed by Oscar Mendez Roca Farell:
=FILTER(A2:A10,
MAP(A2:A10,
LAMBDA(a,
LET(_f,
ROW(
1:10
)-1,
_n,
DROP(
FREQUENCY(
--MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
_f
),
-1
),
AND(ISEVEN(+TOCOL((_n+MOD(
_f,
2
)/_n^0),
2)))))))Excel solution 10 for Check Digit Occurrence Validity, proposed by Sunny Baggu:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
x,
LET(
num,
--MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
seq,
SEQUENCE(
,
10,
0
),
_a,
MAP(
seq,
LAMBDA(
a,
SUM(
N(
num = a
)
)
)
),
AND(
MOD(
WRAPROWS(
IF(
_a,
_a,
seq
),
2
),
2
) = {0,
1}
)
)
)
)
)Excel solution 11 for Check Digit Occurrence Validity, proposed by Sunny Baggu:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
num,
LET(
_m,
--MID(
num,
SEQUENCE(
LEN(
num
)
),
1
),
_even,
TOCOL(
IF(
MOD(
_m,
2
) = 0,
_m,
1 / 0
),
3
),
_ueven,
UNIQUE(
_even
),
_ce,
MAP(
_ueven,
LAMBDA(
a,
SUM(
N(
_even = a
)
)
)
),
_cce,
AND(
MAP(
_ce,
LAMBDA(
a,
ISEVEN(
a
)
)
)
),
_odd,
TOCOL(
IF(
MOD(
_m,
2
) <> 0,
_m,
1 / 0
),
3
),
_uodd,
UNIQUE(
_odd
),
_co,
MAP(
_uodd,
LAMBDA(
a,
SUM(
N(
_odd = a
)
)
)
),
_coo,
AND(
MAP(
_co,
LAMBDA(
b,
ISODD(
b
)
)
)
),
AND(
_cce,
_coo
)
)
)
)
)Excel solution 12 for Check Digit Occurrence Validity, proposed by LEONARD OCHEA 🇷🇴:
=LET(x,A2:A10,FILTER(x,MAP(x,LAMBDA(a,LET(n,LEN(a),u,UNIQUE(MID(a,SEQUENCE(n),1)),v,n-LEN(SUBSTITUTE(a,u,"")),AND(MOD(u,2)=MOD(v,2)))))))Excel solution 13 for Check Digit Occurrence Validity, proposed by Abdallah Ally:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
v,
LET(
a,
v,
b,
UNIQUE(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
& )
),
REDUCE(
TRUE,
b,
LAMBDA(
x,
y,
IF(
ISODD(
y
),
AND(
x,
ISODD(
LEN(
a
)-LEN(
SUBSTITUTE(
a,
y,
""
)
)
)
),
AND(
x,
ISEVEN(
LEN(
a
)-LEN(
SUBSTITUTE(
a,
y,
""
)
)
)
)
)
)
)
)
)
)
)Excel solution 14 for Check Digit Occurrence Validity, proposed by Abdallah Ally:
=FILTER(A2:A10,
MAP(A2:A10,
LAMBDA(x,
LET(a,
x,
b,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
REDUCE(1,
b,
LAMBDA(x,
y,
x*(ISODD(
y
)*ISODD(SUM(--(b=y)))+ISEVEN(
y
)*ISEVEN(SUM(--(b=y))))))))))Excel solution 15 for Check Digit Occurrence Validity, proposed by Asheesh Pahwa:
=FILTER(A2:A9,
LET(m,
MAP(A2:A9,
LAMBDA(z,
LET(In,
LEN(
z
),
sq,
MID(
z,
SEQUENCE(
In
),
1
),
cnt,
BYROW(sq,
LAMBDA(x,
SUM(--(sq=x)))),
_odd,
ISODD(
sq
),
flt,
UNIQUE(
FILTER(
sq,
_odd
)
),
xlpd,
--(XLOOKUP(
flt,
sq,
cnt
)),
isod,
ISODD(
xlpd
),
AND(VSTACK(isod,
ISEVEN(-- (XLOOKUP(
UNIQUE(
FILTER(
sq,
NOT(
_odd
)
)
),
sq,
cnt
)))))))),
m))Excel solution 16 for Check Digit Occurrence Validity, proposed by Julien Lacaze:
=FILTER(A2:A10,
MAP(A2:A10,LAMBDA(a,LET(s,MID(a,SEQUENCE(LEN(a)),1),
c,MAP(s,LAMBDA(d,SUM(--(d=s)))),
AND(BITAND(s,1)=BITAND(c,1))))))Excel solution 17 for Check Digit Occurrence Validity, proposed by Pieter de Bruijn:
=LET(
a,
A2:A10,
FILTER(
a,
MAP(
a,
LAMBDA(
b,
LET(
s,
ROW(
$1:$5
)*2-1,
LET(
o,
LEN(
b
)-LEN(
SUBSTITUTE(
b,
s,
""
)
),
e,
LEN(
b
)-LEN(
SUBSTITUTE(
b,
s-1,
""
)
),
AND(
ISODD(
FILTER(
o,
o
)
),
ISEVEN(
FILTER(
e,
e
)
)
)
)
)
)
)
)
)Excel solution 18 for Check Digit Occurrence Validity, proposed by Giorgi Goderdzishvili:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
x,
LET(
nm,
TEXT(
x,
"0"
),
chrs,
--MID(
nm,
SEQUENCE(
,
LEN(
nm
)
),
1
),
odd,
ISODD(
--chrs
),
even,
ISEVEN(
--chrs
),
cnt,
LEN(
nm
)-LEN(
SUBSTITUTE(
nm,
chrs,
""
)
),
odd_chck,
SUM(
--ISEVEN(
--IF(
odd,
cnt,
1
)
)
)=0,
even_chck,
SUM(
--ISODD(
-- IF(
even,
cnt,
0
)
)
)=0,
odd_chck*even_chck
)
)
)
)Excel solution 19 for Check Digit Occurrence Validity, proposed by Daniel Garzia:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
x,
LET(
n,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
AND(
MAP(
UNIQUE(
n
),
LAMBDA(
r,
LET(
c,
ISODD(
SUM(
IF(
n=r,
1
)
)
),
IF(
ISODD(
r
),
c,
c-1
)
)
)
)
)
)
)
)
)Excel solution 20 for Check Digit Occurrence Validity, proposed by Quadri Olayinka Atharu:
=FILTER(A2:A10,MAP(A2:A10,LAMBDA(_num,LET(l,LEN(_num),
s,--MID(_num,SEQUENCE(l),1),
f,MAP(s,LAMBDA(x,SUM(N(s=x)))),
o,ISODD(f)*ISODD(s),
e,ISEVEN(f)*ISEVEN(s),
SUM(o+e)=l))))Excel solution 21 for Check Digit Occurrence Validity, proposed by Paolo Pozzoli:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
n,
LET(
num,
n,
split,
INT(
MID(
num,
SEQUENCE(
LEN(
num
)
),
1
)
),
uniq,
SORT(
UNIQUE(
split
)
),
cnt,
HSTACK(
uniq,
MAP(
uniq,
LAMBDA(
n;COUNT(
FILTER(
split,
split=n
)
)
)
)
),
AND(
BYROW(
cnt,
LAMBDA(
r,
MOD(
SUM(
r
),
2
)=0
)
)
)
)
)
)
)Excel solution 22 for Check Digit Occurrence Validity, proposed by samir tobeil:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
x,
LET(
e,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)*1,
r,
UNIQUE(
e
),
p,
CONCAT(
IF(
ISODD(
r
),
1,
2
)
),
t,
FREQUENCY(
e,
e
),
s,
CONCAT(
IF(
t=0,
"",
IF(
ISODD(
t
),
1,
2
)
),
),
p=s
)
)
)
)Excel solution 23 for Check Digit Occurrence Validity, proposed by samir tobeil:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
x,
LET(
e,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)*1,
r,
UNIQUE(
e
),
p,
CONCAT(
IF(
ISODD(
r
),
1,
2
)
),
t,
FREQUENCY(
e,
e
),
s,
CONCAT(
IF(
t=0,
"",
IF(
ISODD(
t
),
1,
2
)
),
),
p=s
)
)
)
)Excel solution 24 for Check Digit Occurrence Validity, proposed by Md Ismail Hosen:
=LAMBDA(Numbers,
LET(fx_One,
LAMBDA(Number,
LET(Digits,
MID(
Number,
SEQUENCE(
LEN(
Number
)
),
1
) * 1,
UniqueDigits,
UNIQUE(
Digits
),
CountGrid,
MMULT(MOD(HSTACK(UniqueDigits,
MMULT(--(UniqueDigits = TOROW(
Digits
)),
SEQUENCE(
ROWS(
Digits
),
,
1,
0
))),
2),
{1;1}),
Result,
AND(
BYROW(
CountGrid,
LAMBDA(
a,
OR(
a = 2,
a = 0
)
)
)
),
Result)),
IsValid,
MAP(
Numbers,
fx_One
),
Result,
FILTER(
Numbers,
IsValid
),
Result))(A2:A10)Excel solution 25 for Check Digit Occurrence Validity, proposed by Amardeep Singh:
= Even
Even + Even = Even
Odd + Even = Odd
MOD function gives the remainder 0 with divisor of 2 and that's what we need in the given problem.
=FILTER(
A2:A10,
BYROW(
A2:A10,
LAMBDA(
y,
LET(
d,
MID(
y,
SEQUENCE(
LEN(
y
)
),
1
),
u,
UNIQUE(
d
),
c,
MAP(
u,
LAMBDA(
x,
ROWS(
FILTER(
d,
d=x
)
)
)
),
SUM(
MOD(
u+c,
2
)
)
)
)
)=0
)Excel solution 26 for Check Digit Occurrence Validity, proposed by Mungunbayar Bat-Ochir:
=LET(
rg;
A2:A10;
FILTER(
rg;
BYROW(
rg;
LAMBDA(
input;
LET(
nums;
VALUE(
MID(
input;
SEQUENCE(
LEN(
input
)
);
1
)
);
nums_unique;
UNIQUE(
nums
);
nums_frequency;
DROP(
FREQUENCY(
nums;
nums_unique
);
-1
);
result;
AND(
MOD(
nums_unique;
2
)=MOD(
nums_frequency;
2
)
);
result
)
)
)
)
)Solving the challenge of Check Digit Occurrence Validity with Excel VBA
Excel VBA solution 1 for Check Digit Occurrence Validity, proposed by Xavier Y.:
*VBA*
==============================
Application
=TRANSPOSE(answer(A2:A10))
==============================
Code
Function answer(a As Range)
Dim rtn() As String
counter = 0
For i = 1 To a.Rows.Count
evenpass = 1
oddpass = 1
For j = 0 To 9
If j Mod 2 = 0 Then
If Not ((Len(a(i, 1)) - Len(Replace(a(i, 1), j, ""))) Mod 2 = 0) Then
evenpass = 0
End If
Else
If ((Len(a(i, 1)) - Len(Replace(a(i, 1), j, ""))) Mod 2 = 0) And (Len(a(i, 1)) - Len(Replace(a(i, 1), j, "")) > 0) Then
oddpass = 0
End If
End If
Next
If evenpass = 1 And oddpass = 1 Then
ReDim Preserve rtn(counter)
rtn(counter) = a(i, 1)
counter = counter + 1
End If
Next
answer = rtn
End Function
&