I need help generating a check digit for a 17 digit number using the Modulus 10 - weight 2 method. Rules below. 21212121212121212

(Modulus 10 – Weight 2)
The algorithm for calculating the check digit is as follows:
1. Working from right to left – multiply every other digit by two. Any carry, the “1” from any two digit product is added to the product of the next multiplication operation. If the product of the final multiplication operation is greater than nine, add the two digits of the product together and if there is a carry from the previous multiplication operation, add it to the sum of the two digits. (As illustrated in the example below for the first 9 in the ORGID: 9 x 2 = 18, 1+8 = 9, add the carry of 1 from the previous multiplication operation: 9 + 1 = 10.)
2. The digits in the products and the digits in the base number not multiplied by two are added together.
3. The total is subtracted from the next higher number ending in zero. If the total ends in zero, the number is subtracted from itself.
4. The difference is the check digit.
Example: ORGID = 9594567 Sequence # = 0123456789 Check Digit= ?
0. Base Number
95945670123456789 - 8

Answer - 8 for check digit. How can I do this for other 17 digit numbers?

Hi Benny,

Am Mon, 29 Dec 2014 22:15:42 +0000 schrieb Benny123:

I need help generating a check digit for a 17 digit number using the
Modulus 10 - weight 2 method. Rules below. 21212121212121212

to sum the digits try:
=IF(LEN(SUMPRODUCT(MID(A1,ROW($1:$17),1)*((2*MOD(R OW($1:$17),2))+(MOD(ROW($1:$17),2)=0))))=2,SUMPROD UCT(MID(SUMPRODUCT(MID(A1,ROW($1:$17),1)*((2*MOD(R OW($1:$17),2))+(MOD(ROW($1:$17),2)=0))),ROW($1:$2) ,1)*1),SUMPRODUCT(MID(SUMPRODUCT(MID(A1,ROW($1:$17 ),1)*((2*MOD(ROW($1:$17),2))+(MOD(ROW($1:$17),2)=0 ))),ROW($1:$3),1)*1))


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Benny,

Am Mon, 29 Dec 2014 22:15:42 +0000 schrieb Benny123:

I need help generating a check digit for a 17 digit number using the
Modulus 10 - weight 2 method. Rules below. 21212121212121212

try it with a macro:
Insert following code in a standard module and call the function on the
sheet with =myCheck(A1) (mdoify the range if your data is not in column
A)

Function myCheck(myRng As Range) As Long
Dim i As Long
Dim tmpSum As Long, mySum As Long

For i = 17 To 1 Step -2
tmpSum = Mid(myRng, i, 1) * 2
If Len(tmpSum) = 2 Then
mySum = mySum + Left(tmpSum, 1) + Right(tmpSum, 1)
Else
mySum = mySum + tmpSum
End If
Next

For i = 16 To 2 Step -2
mySum = mySum + Mid(myRng, i, 1)
Next
myCheck = mySum - WorksheetFunction.Round(mySum, -1)
End Function


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Claus

I was not able to get it to work. Do you think you could email me the excel as this is very complicated and I am desperate?

I can be reached at k3benson3

at

hotmail

The number that I am looking for is:

10098987203140227 and then the check digit. Please email me this in an excel and email to the above email. I broke up the email address...

Hi Benny,

Am Tue, 30 Dec 2014 18:21:38 +0000 schrieb Benny123:

10098987203140227 and then the check digit. Please email me this in an
excel and email to the above email. I broke up the email address...

I sent it to hotmail.com
The address yahoo.com is invalid


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Kevin,

Am Tue, 30 Dec 2014 18:21:38 +0000 schrieb Benny123:

The number that I am looking for is:

10098987203140227 and then the check digit.

try it with a function:

Function NewCheck(myRng As Range) As Long
Dim No1 As String
Dim mySum As Long, i As Long

For i = 1 To 17 Step 2
No1 = No1 & Mid(myRng, i, 1)
Next

For i = 2 To 16 Step 2
mySum = mySum + Mid(myRng, i, 1)
Next

No1 = 2 * No1
For i = 1 To Len(No1)
mySum = mySum + Mid(No1, i, 1)
Next

NewCheck = WorksheetFunction.RoundUp(mySum, -1) - mySum
End Function


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional