Check Digit:- A Real Challenge!

[Duncan]

Hi all

I know that this is really way out of my league, I would love for
someone to come up with some code to check if a number is valid using
the check digit method I will paste below. (a 7 digit number which
really should be a 6 digit number with the 7th being the check digit)

I can just about work it out using a spreadsheet with loads of formulas
dotted about but would have a clue how to code something to check a
number according to this method.

Hope someone can help

Regards

Duncan

(see below for method)

The check digit is at the end of the number (7 digit number).

The check digit is calculated from the base number as follows: (base
number is first 6 digits)

Multiply digit 1 of the base number by 13,
digit 2 by 11,
digit 3 by 7,
digit 4 by 5,
digit 5 by 3,
digit 6 by 2

and add the results of these multiplications together

Divide the overall sum above by 11 and obtain the remainder.

Subtract the remainder from 11 to give the check digit.

If the check digit value is 11, change it to 0.
If the check digit value is 10, add 1 to the base number and repeat the
process.

Suffix the check digit to the base number to give the registration
number.

Note that with this in place, there will obviously be a 1 in 10 chance
of entering a 'valid' number which won't necessarily be correct.

 

[C01d]

Function checkDigit()
num = Range("a1").Value
remainder = (Mid(num, 1, 1) * 13 + Mid(num, 2, 1) * 11 + Mid(num
3, 1) * 7 + Mid(num, 4, 1) * 5 + Mid(num, 5, 1) * 3 + Mid(num, 6, 1)
2) Mod 11
check = 11 - remainder

While check = 10
check = check + 1
check = 11 - check
Wend

If check = 11 Then
check = 0
End If

checkDigit = check
End Functio

 

[Duncan]

C01d,

You certainly are a champ, works perfectly!

Many many thanks

Duncan

 

[Bob Phillips]

How about

=MOD(11-MOD(SUMPRODUCT(MID(A1,ROW(INDIRECT("1:6")),1)*{13;11;7;5;3;2}),11),1
1)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bob Phillips]

slight change

=MOD(MOD(11-MOD(SUMPRODUCT(MID(A1,ROW(INDIRECT("1:6")),1)*{13;11;7;5;3;2}),1
1),11),10)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Duncan]

Thank you for the spreadsheet way Bob, but im trying to get this to
work upon entry to collumn A, just to check if the number entered is a
valid number, and if it is not then to colour it red.

But I am not really getting there!, I am trying to adapt this code
given to me kindly but Im not succeeding at all!

So far I have:

Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveCell.Column = 1 Then

On Error Resume Next

num = ActiveCell.Offset(-1, 0).Value
Set num2 = ActiveCell.Offset(-1, 0)
remainder = (Mid(num, 1, 1) * 13 + Mid(num, 2, 1) * 11 + Mid(num,
3, 1) * 7 + Mid(num, 4, 1) * 5 + Mid(num, 5, 1) * 3 + Mid(num, 6, 1) *
2) Mod 11
check = 11 - remainder

While check = 10
check = check + 1
check = 11 - check
Wend

If check = 11 Then
check = 0
End If

'checkDigit = check
num3 = (Mid(num, 1, 6))


finnum = num3 & check

If finnum <> num2 Then
num2.Select
ActiveCell.Interior.ColorIndex = 3
End If


'to check if its working
'MsgBox check
End If


End Sub

 

[Duncan]

Ive changed it since, I think its working but im not sure!

Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveCell.Column = 1 Then

On Error Resume Next
num = ActiveCell.Offset(-1, 0).Value
Set num2 = ActiveCell.Offset(-1, 0)
remainder = (Mid(num, 1, 1) * 13 + Mid(num, 2, 1) * 11 + Mid(num,
3, 1) * 7 + Mid(num, 4, 1) * 5 + Mid(num, 5, 1) * 3 + Mid(num, 6, 1) *
2) Mod 11
check = 11 - remainder

While check = 10
check = check + 1
check = 11 - check
Wend

If check = 11 Then
check = 0
End If

'checkDigit = check
num3 = (Mid(num, 1, 6))

finnum = num3 & check
'MsgBox Trim(finnum)

If Trim(finnum) <> Trim(num2) Then
num2.Select
ActiveCell.Interior.ColorIndex = 3
End If

End If


End Sub

 

[aidan.heritage]

I'm not 100% sure about the final bit of the check digit (If the check
digit value is 10, add 1 to the base number and repeat the process) but
you can use DATA VALIDATION to handle this - ignoring the final bit the
validation goes like this

=AND(LEN(DATATOCHECK)=7,MOD((MID(DATATOCHECK,1,1)*13)+(MID(DATATOCHECK,2,1)*11)+(MID(DATATOCHECK,3,1)*7)+(MID(DATATOCHECK,4,1)*5)+(MID(DATATOCHECK,5,1)*3)+(MID(DATATOCHECK,6,1)*2)+RIGHT(DATATOCHECK,1),11)=0)

replace datatocheck with the actual cell reference - this is entered as
CUSTOM on the validation choice.

I'm ASSUMING you are locked in to the check digit mechanism, otherwise
would suggest using A as a valid check digit (base 11!)

 

[Duncan]

This works good aiden but it wont work on the entries that result in a
check digit of 10, dont know if this could be worked around cos its
like saying run the validation again but first add to the constant, not
sure if thats possible in data validation.

I wish they wouldnt use such a stupid method for check digit! its like
reiventing the wheel, and leaving out the tyre!

ah well I have to work with this method as its what is used and I cant
change that, im really new to check digits, shame really.

Regards

Duncan

 

[aidan.heritage]

I'm not sure how we'd get a check digit of 10, because you change the
digits accordingly - so wouldn't it therefore produce a check digit
still in the range 0-10?

 

[Duncan]

supposedly,

but if you try it both ways, the number 3124420 has a check digit of 0
at the end only because the base 312442 results in a check digit of 10
and rerunning after adding 1 to the base produces 0, but the data
validation way will not allow this number to be input.

very confusing stuff, I think its still going over my head really.

 

[aidan.heritage]

I think I'm getting there - the ACTUAL number isn't modified, so a
check digit of 0 could be EITHER 0 or 10? If so, we can modify the
rather long validation string to be an even longer validation string
using the OR function - it would be (or(mod(etc etc etc)=0,mod(etc etc
etc)=10)

 

[Duncan]

To be honest Aiden, im not too sure myself!, I have been battling with
this for hours now and as I cannot really get my head around the
formula itself I have resorted to the vba code for the answer,

It would have been so much better to have it respond to data validation
but I think because "If the check digit value is 10, add 1 to the base
number and repeat the process." involves looping round again it keeps
giving me wrong answers.

I think I have it working fine now in the code (which I will post below
for the benefit of others) and I am going to give the validation way a
rest for now!

Many thanks for your help on this.

Duncan

(below code allows for TAB or ENTER away from cell, im sure this is not
the most efficient but it works! (I think))

Private Sub Worksheet_Change(ByVal Target As Range)

If ActiveCell.Column = 1 Then

On Error Resume Next

num = ActiveCell.Offset(-1, 0).Value
Set num2 = ActiveCell.Offset(-1, 0)
remainder = (Mid(num, 1, 1) * 13 + Mid(num, 2, 1) * 11 +
Mid(num, 3, 1) * 7 + Mid(num, 4, 1) * 5 + Mid(num, 5, 1) * 3 + Mid(num,
6, 1) * 2) Mod 11
check = 11 - remainder

While check = 10
check = check + 1
check = 11 - check
Wend

If check = 11 Then
check = 0
End If

num3 = (Mid(num, 1, 6))

FINnum = num3 & check

If Trim(FINnum) <> Trim(num2) Then
num2.Select
ActiveCell.Interior.ColorIndex = 3
MsgBox "This is not a Valid Registration Number!"
End If

If Trim(FINnum) = Trim(num2) Then
If num2.Interior.ColorIndex = 3 Then
num2.Select
ActiveCell.Interior.ColorIndex = 0
End If
End If

End If

If ActiveCell.Column = 2 Then

On Error Resume Next

NUM0 = ActiveCell.Offset(0, -1).Value
Set NUM22 = ActiveCell.Offset(0, -1)
REMAINDERR = (Mid(NUM0, 1, 1) * 13 + Mid(NUM0, 2, 1) * 11 +
Mid(NUM0, 3, 1) * 7 + Mid(NUM0, 4, 1) * 5 + Mid(NUM0, 5, 1) * 3 +
Mid(NUM0, 6, 1) * 2) Mod 11
CHECKK = 11 - REMAINDERR

While CHECKK = 10
CHECKK = CHECKK + 1
CHECKK = 11 - CHECKK
Wend

If CHECKK = 11 Then
CHECKK = 0
End If

NUM33 = (Mid(NUM0, 1, 6))

F1nN3m = NUM33 & CHECKK

If Trim(F1nN3m) <> Trim(NUM22) Then
NUM22.Select
ActiveCell.Interior.ColorIndex = 3
MsgBox "This is not a Valid Registration Number!"
End If

If Trim(F1nN3m) = Trim(NUM22) Then
If NUM22.Interior.ColorIndex = 3 Then
NUM22.Select
ActiveCell.Interior.ColorIndex = 0
End If
End If

End If




End Sub