Check Digit:- A Real Challenge!
Duncan
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 Function
--
C01d
------------------------------------------------------------------------
C01d's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=34422
View this thread: http://www.excelforum.com/showthread.php?threadid=548907
Duncan
You certainly are a champ, works perfectly!
Many many thanks
Duncan
Bob Phillips
=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)
"C01d" <C01d.28zakc_1...@excelforum-nospam.com> wrote in message
news:C01d.28zakc_1...@excelforum-nospam.com...
Bob Phillips
=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)
"C01d" <C01d.28zakc_1...@excelforum-nospam.com> wrote in message
news:C01d.28zakc_1...@excelforum-nospam.com...
>
Duncan
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
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.h...@virgin.net
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
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.h...@virgin.net
digits accordingly - so wouldn't it therefore produce a check digit
still in the range 0-10?
Duncan
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.h...@virgin.net
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
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