MOD 10 Check Digit Formula

[indeegirl]

Hi! I hope someone can help me out! I am a graphic designer, and I am designing a statement for an insurance company. They have a slip that needs a MOD 10 Check Digit scan line with a weight of 2 1 at the bottom of the payment slip. The customer provides me with a .csv file with the following column titles that need to be converted to this scan line. The position is the placement in the scan line.

Position Description
1-10 ACCT_ID
11-13 Blank
14-21 INV_DATE Date (yyyymoda format, however the data comes to me as 1/8/2011)
22-25 PLAN_ID
26 CREDIT_AMT AND DEBIT_AMT
27-32 AMT_DUE
33 Check Digit* Will be generated by all of these numbers

The resulting scan line should look like (the following the # means space, I don't need the actual symbol there):
1000143400###20091201000000045801

I am using XMPie, a variable data software to make the statement, so I need excel to formulate this code, so I can map it into my document. Another thing, is the date format I receive is wrong, compared to how it is needed in the Scanline. Will I need to change that before generating the code? This project has got me baffled, and the more I try the crazier it seems. If someone has any ideas, I would be forever in your debt The customer wants this project to go live March 3rd, and I have been working on this for a month!! If you can help, I would be able to email you the statement, the execl file and whatever else you need!!

 

[indeegirl]

Awesome! Now, will the decimal and the - sign interfere with the check digit formula?

 

[barry houdini]

Yes, I assumed you would only have numbers......you'd also have to deal with the spaces, I'm not sure if those should just be completely ignored or treated as zeroes......do you know?

 

[indeegirl]

I may have to ask the customer how they dealt with the - and the . previously. They actually do need a space, so the final number looks like this: (### is a space, when I post, it takes the spaces away) the 1 is the check digit.

1000143400###20091201000000045801

Thanks so much everyone for helping me with this!

 

[indeegirl]

For the spaces, what I could do is just use the CONCATENATED_DATA to calculate the check digit in excel, then map the account number and the other data separately in my variable data software. That would take care of the space issue! As far as calculating my check digit, I am still at a loss :|

 

[Manikandan g]

Need calculation Formula Urgent!!!!!!!!!!!

For your reference:
A B C

<table border="0" cellpadding="0" cellspacing="0" width="200"><col style="width: 32pt;" width="42"> <col style="width: 56pt;" width="74"> <col style="width: 63pt;" width="84"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 32pt;" align="right" height="20" width="42"> 12.5</td> <td class="xl65" style="border-left: medium none; width: 56pt;" align="right" width="74">30</td> <td class="xl65" style="border-left: medium none; width: 63pt;" align="right" width="84">375</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" align="right" height="20">12.5</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">45</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">562.5</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" align="right" height="20">5</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">25</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">125</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" align="right" height="20">5</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">15</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">75</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" align="right" height="20">12.5</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">10</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">125</td> </tr> </tbody></table>
Total 5 :
Total 12.5:

Total No of 5 in the A column is 2, But i need a formula in another column=
Total No of 5(A column) * opposite C column amount

 

[indeegirl]

Re: Need calculation Formula Urgent!!!!!!!!!!!

Thanks! I don't understand. Maybe it's to early in the morn for me yet

 

[texasalynn]

Re: Need calculation Formula Urgent!!!!!!!!!!!

For your reference:
A B C

<TABLE cellSpacing=0 cellPadding=0 width=200 border=0><COLGROUP><COL style="WIDTH: 32pt" width=42><COL style="WIDTH: 56pt" width=74><COL style="WIDTH: 63pt" width=84><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="WIDTH: 32pt; HEIGHT: 15pt" align=right width=42 height=20>12.5</TD><TD class=xl65 style="BORDER-LEFT: medium none; WIDTH: 56pt" align=right width=74>30</TD><TD class=xl65 style="BORDER-LEFT: medium none; WIDTH: 63pt" align=right width=84>375</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-TOP: medium none; HEIGHT: 15pt" align=right height=20>12.5</TD><TD class=xl65 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=right>45</TD><TD class=xl65 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=right>562.5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-TOP: medium none; HEIGHT: 15pt" align=right height=20>5</TD><TD class=xl65 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=right>25</TD><TD class=xl65 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=right>125</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-TOP: medium none; HEIGHT: 15pt" align=right height=20>5</TD><TD class=xl65 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=right>15</TD><TD class=xl65 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=right>75</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-TOP: medium none; HEIGHT: 15pt" align=right height=20>12.5</TD><TD class=xl65 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=right>10</TD><TD class=xl65 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=right>125</TD></TR></TBODY></TABLE>
Total 5 :
Total 12.5:

Total No of 5 in the A column is 2, But i need a formula in another column=
Total No of 5(A column) * opposite C column amount

Manikandan - did you put this to the wrong post?

 

[indeegirl]

Re: Need calculation Formula Urgent!!!!!!!!!!!

Good, I thought I was losing it!