Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Digit:- A Real Challenge!
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Digit:- A Real Challenge!
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 -- C01 ----------------------------------------------------------------------- C01d's Profile: http://www.excelforum.com/member.php...fo&userid=3442 View this thread: http://www.excelforum.com/showthread.php?threadid=54890 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Digit:- A Real Challenge!
C01d,
You certainly are a champ, works perfectly! Many many thanks Duncan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Digit:- A Real Challenge!
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) "C01d" wrote in message ... 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...o&userid=34422 View this thread: http://www.excelforum.com/showthread...hreadid=548907 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Digit:- A Real Challenge!
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) "C01d" wrote in message ... 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...o&userid=34422 View this thread: http://www.excelforum.com/showthread...hreadid=548907 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Digit:- A Real Challenge!
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 Bob Phillips wrote: 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) "C01d" wrote in message ... 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...o&userid=34422 View this thread: http://www.excelforum.com/showthread...hreadid=548907 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Digit:- A Real Challenge!
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Digit:- A Real Challenge!
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 wrote: 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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Digit:- A Real Challenge!
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 wrote: 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 wrote: 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 wrote: 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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Digit:- A Real Challenge!
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. wrote: 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 wrote: 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 wrote: 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 wrote: 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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Digit:- A Real Challenge!
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 wrote: 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. wrote: 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 wrote: 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 wrote: 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 wrote: 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. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Digit:- A Real Challenge!
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Real Data Formatting Challenge | Excel Discussion (Misc queries) | |||
Challenge with spell check with multiple worksheets | Excel Discussion (Misc queries) | |||
A real challenge for you!! | Excel Discussion (Misc queries) | |||
A challenge for a real Excel Expert (Bob Phillips for instance) | Excel Discussion (Misc queries) | |||
A real challenge...can anyone solve this !??!? | Excel Programming |