Check Digit Mod 10 with weights 7-5-3-2

[fastmr291]

I need to create a check digit for a financial institution's scanline and am not quite sure how to make that happen using the 7532 weights. The appeal code and package code should be static so I will just assign the appropriate numbers for the letters and will build one string containing all of the numbers for the scanline without spaces. That is unless I should do it a different way. I found some VB code but I am unsure how to use it so if we can get everything to work in Excel or Access that would be wonderful. Thank you for your assistance and I await your responses.

~Chris


The instructions I received are as follows...

Total # of characters: 34
Font: OCR-A


The scan line is 34 characters long including all spaces. It is as follows:

POSITIONS START-END LENGTH DESCRIPTION
1-10 10 ID#¹
11 1 Space
12-17 6 Fund²
18 1 Space
19-24 6 Appeal³
25 1 Space
26-32 7 Package⁴
33 1 Space
34 1 Check Digit Verifier<sup>5


1</sup> – ID# - all ID#’s from the client should be 10 characters

² – Fund - always 451000

³ – Appeal – DM# - 0 fill after DM if necessary (ie DM0213)

⁴ – Package – Keycode (ie 405R914)

⁵ – Check digit (for whole scan line)



Alpha Conversion

[TABLE="width: 285, align: left"]
<tbody>[TR]
[TD]Numeric Value
[/TD]
[TD="colspan: 3"]Alpha Value
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]A
[/TD]
[TD]J
[/TD]
[TD]S
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]B
[/TD]
[TD]K
[/TD]
[TD]T
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]C
[/TD]
[TD]L
[/TD]
[TD]U
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]D
[/TD]
[TD]M
[/TD]
[TD]V
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]E
[/TD]
[TD]N
[/TD]
[TD]W
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]F
[/TD]
[TD]O
[/TD]
[TD]X
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]G
[/TD]
[TD]P
[/TD]
[TD]Y
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]H
[/TD]
[TD]Q
[/TD]
[TD]Z
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]I
[/TD]
[TD]R
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 285"]
<tbody>[TR]
[TD]
[/TD]
[TD="colspan: 3"]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]




[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]
Check Digit (CD) Routine – client required calculation


1. Identify the series of numbers
Ex. 1000108030 451000 DM0213 405R914

2. Convert any alpha characters to a number based on the conversion chart above

[TABLE="width: 718"]
<tbody>[TR]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]8
[/TD]
[TD]0
[/TD]
[TD]3
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD]D
[/TD]
[TD]M
[/TD]
[TD]0
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]0
[/TD]
[TD]5
[/TD]
[TD]R
[/TD]
[TD]9
[/TD]
[TD]1
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]8
[/TD]
[TD]0
[/TD]
[TD]3
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]0
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]0
[/TD]
[TD]5
[/TD]
[TD]9
[/TD]
[TD]9
[/TD]
[TD]1
[/TD]
[TD]4
[/TD]
[/TR]
</tbody>[/TABLE]

3. Assign weights from left to right 7,5,3,2

[TABLE="width: 727"]
<tbody>[TR]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]8
[/TD]
[TD]0
[/TD]
[TD]3
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]0
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]0
[/TD]
[TD]5
[/TD]
[TD]9
[/TD]
[TD]9
[/TD]
[TD]1
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]5
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]7
[/TD]
[TD]5
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]7
[/TD]
[TD]5
[/TD]
[TD][/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]7
[/TD]
[TD]5
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD]7
[/TD]
[TD]5
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]7
[/TD]
[TD]5
[/TD]
[TD][/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]7
[/TD]
[TD]5
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]7
[/TD]
[/TR]
</tbody>[/TABLE]


4. Multiply each number by its assigned weight

[TABLE="width: 814"]
<tbody>[TR]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]8
[/TD]
[TD]0
[/TD]
[TD]3
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]0
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]0
[/TD]
[TD]5
[/TD]
[TD]9
[/TD]
[TD]9
[/TD]
[TD]1
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]5
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]7
[/TD]
[TD]5
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]7
[/TD]
[TD]5
[/TD]
[TD]
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]7
[/TD]
[TD]5
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]
[/TD]
[TD]7
[/TD]
[TD]5
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]7
[/TD]
[TD]5
[/TD]
[TD]
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]7
[/TD]
[TD]5
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]7
[/TD]
[TD]0
[/TD]
[TD]24
[/TD]
[TD]0
[/TD]
[TD]21
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD]12
[/TD]
[TD]10
[/TD]
[TD]7
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD]28
[/TD]
[TD]20
[/TD]
[TD]0
[/TD]
[TD]4
[/TD]
[TD]7
[/TD]
[TD]15
[/TD]
[TD][/TD]
[TD]12
[/TD]
[TD]0
[/TD]
[TD]35
[/TD]
[TD]45
[/TD]
[TD]27
[/TD]
[TD]2
[/TD]
[TD]28
[/TD]
[/TR]
</tbody>[/TABLE]


5. Sum the digits of the results (1^st sum the numbers with 2 digits and then sum the entire line, ie: 24 = 2+6=8)

[TABLE="width: 775"]
<tbody>[TR]
[TD]7
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]7
[/TD]
[TD]0
[/TD]
[TD]24
[/TD]
[TD]0
[/TD]
[TD]21
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD]12
[/TD]
[TD]10
[/TD]
[TD]7
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD]28
[/TD]
[TD]20
[/TD]
[TD]0
[/TD]
[TD]4
[/TD]
[TD]7
[/TD]
[TD]15
[/TD]
[TD][/TD]
[TD]12
[/TD]
[TD]0
[/TD]
[TD]35
[/TD]
[TD]45
[/TD]
[TD]27
[/TD]
[TD]2
[/TD]
[TD]28
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]7
[/TD]
[TD]0
[/TD]
[TD]2+4=6
[/TD]
[TD]0
[/TD]
[TD]2+1=3
[/TD]
[TD]0
[/TD]
[TD]
[/TD]
[TD]1+2=3
[/TD]
[TD]1+0=1
[/TD]
[TD]7
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]
[/TD]
[TD]2+8=10
[/TD]
[TD]2+0=2
[/TD]
[TD]0
[/TD]
[TD]4
[/TD]
[TD]7
[/TD]
[TD]1+5=6
[/TD]
[TD]
[/TD]
[TD]1+2=3
[/TD]
[TD]0
[/TD]
[TD]3+5=8
[/TD]
[TD]4+5=9
[/TD]
[TD]2+7=9
[/TD]
[TD]2
[/TD]
[TD]2+8=10
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]7
[/TD]
[TD]7
[/TD]
[TD]7
[/TD]
[TD]14
[/TD]
[TD]14
[/TD]
[TD]20
[/TD]
[TD]20
[/TD]
[TD]23
[/TD]
[TD]23
[/TD]
[TD][/TD]
[TD]26
[/TD]
[TD]27
[/TD]
[TD]34
[/TD]
[TD]34
[/TD]
[TD]34
[/TD]
[TD]34
[/TD]
[TD][/TD]
[TD]44
[/TD]
[TD]46
[/TD]
[TD]46
[/TD]
[TD]50
[/TD]
[TD]57
[/TD]
[TD]63
[/TD]
[TD][/TD]
[TD]66
[/TD]
[TD]66
[/TD]
[TD]74
[/TD]
[TD]83
[/TD]
[TD]92
[/TD]
[TD]94
[/TD]
[TD]104
[/TD]
[/TR]
</tbody>[/TABLE]


6. Divide the sum by the modulus 10 to determine the remainder
Ex: 104/10=10.4 – the remainder is 4

7. Subtract the remainder* from the Modulus 10 to determine the check digit
* If the remainder is 0, then the check digit equals 0
Ex: 10-4=6 – the check digit is 6

 

[shg]

You have an example where the check digit is 10?

 

[thedoccontroller]

I do. Unfortunately it's at the office. I'll post it tomorrow if that's okay. Thanks for the quick response

 

[thedoccontroller]

Hi,

Alternative, formula-based solution:

Array formula**:

=10-MOD(SUM(0+MID(TEXT(IF(1-ISNUMBER(0+MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),1+MOD(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))-65,9),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*MID(7532,1+MOD(ROW(INDIRECT("1:"&LEN(A1)))-1,4),1),"00"),{1,2},1)),10)

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[TABLE="width: 524"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Given[/TD]
[TD]Return[/TD]
[TD]CK#[/TD]
[/TR]
[TR]
[TD]0663711DM0116LCLCLAF916[/TD]
[TD]0663711DM0116LCLCLAF91610[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]0813016DM1022LCLCLBS258[/TD]
[TD]0813016DM1022LCLCLBS25810[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]15456524VGFX255415GF542[/TD]
[TD]15456524VGFX255415GF5423[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]25715658FFCH2365FFD3652[/TD]
[TD]25715658FFCH2365FFD36525[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]254745544JDSUJN4562455214[/TD]
[TD]254745544JDSUJN45624552145[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]26585425654HJDSAGDHG454454[/TD]
[TD]26585425654HJDSAGDHG4544545[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
</tbody>[/TABLE]
=10-MOD(SUM(0+MID(TEXT(IF(1-ISNUMBER(0+MID(C2,ROW(INDIRECT("1:"&LEN(C2))),1)),1+MOD(CODE(MID(C2,ROW(INDIRECT("1:"&LEN(C2))),1))-65,9),MID(C2,ROW(INDIRECT("1:"&LEN(C2))),1))*MID(7532,1+MOD(ROW(INDIRECT("1:"&LEN(C2)))-1,4),1),"00"),{1,2},1)),10)

Same formula, just changed the references. Not sure how to attach the file yet. Not sure I can yet. But here's a cloud link: https://onedrive.live.com/redir?page=view&resid=BC41855B1EB6024B!7300&authkey=!APvDml58FlGteR0

 

[XOR LX]

Hi.

You haven't followed the instructions re how to enter an array formula at the foot of my post.

Regards

 

[thedoccontroller]

Thats cuz sometimes I'm a moron. Thanks. And thank you for being a genius. I have learned so much from this site.

 

[XOR LX]

No worries! We all do it!

And you're welcome!

Cheers