Dear all,

I have the following serial numbers:

41078928128
41078928136
41078928144
41078928151
41078928169
41078928177
41078928185
41078928193
41078928201

The last digit is a check digit. How can I get the formula /algorithm
to get the check digit? So that if I have the serial numbers I can add
the check digit with it?

Thanks

see:

http://groups.google.com/group/micro...d7fd6324bfa384
--
Gary''s Student - gsnu200793


"Abdul" wrote:

> Dear all,
>
> I have the following serial numbers:
>
> 41078928128
> 41078928136
> 41078928144
> 41078928151
> 41078928169
> 41078928177
> 41078928185
> 41078928193
> 41078928201
>
> The last digit is a check digit. How can I get the formula /algorithm
> to get the check digit? So that if I have the serial numbers I can add
> the check digit with it?
>
> Thanks
>
>

hi
assuming all of your serial numbers are the same length and start in A1,
this formula will get your check digit
=right(A1,1)
but i don't understand the rest of your question.


Regards
FSt1

"Abdul" wrote:

> Dear all,
>
> I have the following serial numbers:
>
> 41078928128
> 41078928136
> 41078928144
> 41078928151
> 41078928169
> 41078928177
> 41078928185
> 41078928193
> 41078928201
>
> The last digit is a check digit. How can I get the formula /algorithm
> to get the check digit? So that if I have the serial numbers I can add
> the check digit with it?
>
> Thanks
>
>

There are lots of different check bit algorithms. It look like you values
meet the Luhn algorithm. I did not check the code below that I got from
en.wikipedia.org. You can get more info from Wikpedia.

Public Function ValidateCardNumber(strCardNumber) As Boolean ' MOD 10 check
digit. "Luhn algorithm"
On Error GoTo Err

Dim intLoop As Integer, intSum As Integer
Dim bIsAlternate As Boolean, intProduct As Integer

For intLoop = Len(strCardNumber) To 1 Step -1

If bIsAlternate = False Then
intSum = intSum + CInt(Mid(strCardNumber, intLoop, 1))
bIsAlternate = True
Else

intProduct = CInt(Mid(strCardNumber, intLoop, 1)) * 2

If Len(CStr(intProduct)) = 2 Then
intSum = intSum + CInt(Mid(intProduct, 1, 1)) +
CInt(Mid(intProduct, 2, 1))
Else
intSum = intSum + CInt(intProduct)
End If

bIsAlternate = False

End If

Next intLoop

If intSum Mod 10 = 0 Then
ValidateCardNumber = True
Else
ValidateCardNumber = False
End If

Exit Function

Err:
MsgBox "Error in ValidateCardNumber()" & vbCrLf & Err.Number &
Err.Description
End Function


"Abdul" wrote:

> Dear all,
>
> I have the following serial numbers:
>
> 41078928128
> 41078928136
> 41078928144
> 41078928151
> 41078928169
> 41078928177
> 41078928185
> 41078928193
> 41078928201
>
> The last digit is a check digit. How can I get the formula /algorithm
> to get the check digit? So that if I have the serial numbers I can add
> the check digit with it?
>
> Thanks
>
>

I think he wants to know how to calculate the 11th digit starting with 10
digit data.
--
Gary''s Student - gsnu200793


"FSt1" wrote:

> hi
> assuming all of your serial numbers are the same length and start in A1,
> this formula will get your check digit
> =right(A1,1)
> but i don't understand the rest of your question.
>
>
> Regards
> FSt1
>
> "Abdul" wrote:
>
> > Dear all,
> >
> > I have the following serial numbers:
> >
> > 41078928128
> > 41078928136
> > 41078928144
> > 41078928151
> > 41078928169
> > 41078928177
> > 41078928185
> > 41078928193
> > 41078928201
> >
> > The last digit is a check digit. How can I get the formula /algorithm
> > to get the check digit? So that if I have the serial numbers I can add
> > the check digit with it?
> >
> > Thanks
> >
> >

Thanks Mike and all other posters.

Great.
The initial testing shows i have got what I am looking for.

Thanks again

On Jun 22, 4:30*pm, "Mike Fogleman" <mikefogle...@comcast.net> wrote:
> Using Joel's algorithm function and a routine to loop through the 10
> possiblecheckdigits, find the one that passes the algorithm and place it
> in the adjacent column. Assuming your serial numbers are in column A,
> starting at row 1, this will place the registration number in column B. The
> actual starting point can be adjusted to fit in the code.
>
> Sub AddCheckNum()
> Dim LRow As Long, ctr As Integer
> Dim TestNum As String
> Dim rng As Range, c As Range
>
> LRow = Cells(Rows.Count, 1).End(xlUp).Row 'change col num to suit
> Set rng = Range("A1:A" & LRow) 'change col & start row to suit
> * * For Each c In rng
> * * * * For ctr = 0 To 9
> * * * * * * TestNum = c.Value & ctr
> * * * * * * If ValidateCardNumber(TestNum) Then
> * * * * * * * * c.Offset(0, 1).Value = TestNum
> * * * * * * * * Exit For
> * * * * * * End If
> * * * * Next
> * * Next
> End Sub
>
> Public Function ValidateCardNumber(strCardNumber) As Boolean ' MOD 10
> checkdigit. "Luhn algorithm"
> * * On Error GoTo Err
>
> * * Dim intLoop As Integer, intSum As Integer
> * * Dim bIsAlternate As Boolean, intProduct As Integer
>
> * * For intLoop = Len(strCardNumber) To 1 Step -1
>
> * * * * If bIsAlternate = False Then
> * * * * * * intSum = intSum + CInt(Mid(strCardNumber, intLoop, 1))
> * * * * * * bIsAlternate = True
> * * * * Else
>
> * * * * * * intProduct = CInt(Mid(strCardNumber, intLoop, 1)) * 2
>
> * * * * * * If Len(CStr(intProduct)) = 2 Then
> * * * * * * * * intSum = intSum + CInt(Mid(intProduct, 1, 1)) +
> CInt(Mid(intProduct, 2, 1))
> * * * * * * Else
> * * * * * * * * intSum = intSum + CInt(intProduct)
> * * * * * * End If
>
> * * * * * * bIsAlternate = False
>
> * * * * End If
>
> * * Next intLoop
>
> * * If intSum Mod 10 = 0 Then
> * * * * ValidateCardNumber = True
> * * Else
> * * * * ValidateCardNumber = False
> * * End If
>
> * * Exit Function
>
> Err:
> * * MsgBox "Error in ValidateCardNumber()" & vbCrLf & Err.Number &
> Err.Description
> End Function
>
> Checkfor wordwrap in the VBA editor after you paste this into a standard
> code module.
> Mike F
>
> "Abdul" <abdulsalam.abdul...@gmail.com> wrote in message
>
> news:d42a50a6-91e7-4161-95f3-(E-Mail Removed)...
>
> > Dear all,
>
> > I have the following serial numbers:
>
> > 41078928128
> > 41078928136
> > 41078928144
> > 41078928151
> > 41078928169
> > 41078928177
> > 41078928185
> > 41078928193
> > 41078928201
>
> > The lastdigitis acheckdigit. How can I get the formula /algorithm
> > to get thecheckdigit? So that if I have the serial numbers I can add
> > thecheckdigitwith it?
>
> > Thanks

> However, for Excel:
> =MOD(789-1,9)+1
> =6

I believe, for Excel's MOD function, you need to use the following instead
of the above?

=MOD(MOD(B1-1,9)+1,9)

Without the second MOD function, numbers like 0, 891, 333, etc. (that is,
numbers whose digits total 9) will return 9 instead of what I think should
be 0. If you do this in VBA, then the second MOD function call should not be
necessary.

Rick

If we wish to get rid of the IF statement, one idea...

Function GenerateCheckDigit(n) As Long
Dim Fg As Boolean 'flag
Dim P As Long
Dim T As Long

'// Odd positions values are doubled
Fg = Len(n) Mod 2 = 1 'Odd?
For P = 1 To Len(n)
T = T + (((1 - Fg) * (Mid(n, P, 1)) - 1) Mod 9) + 1
Fg = Not Fg
Next P
GenerateCheckDigit = (10 - (T Mod 10)) Mod 10
End Function

--
Dana DeLouis