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!!

 

[texasalynn]

Welcome to MrExcel board...

ok without seeing a sample of your data, not sure I can help, but the data issue can be solved by using
=text(b2,"yyyymmdd")
changing the reference of B2 to where your data is stored.

 

[indeegirl]

Thanks for the quick response! I will try that with my date. Could I email you the sample data? For some reason, I can't send attachments on here.

 

[texasalynn]

no attachments are not allowed. You can post samples with different methods. See the - Posting guidelines, forum rules and terms of use


here is a sample using code that can be received by posting a PM to Richard Schollar

Excel Workbook
	A	B
24	1/15/2011	my date 40558
25		my date 20110115
Sheet2

Excel 2003

Cell Formulas
Range	Formula
B24	="my date "&A24
B25	="my date "&TEXT(A24,"yyyymmdd")

 

[indeegirl]

I just entered that for the date! Fantastic!!!!!! Exactly what I need!!!

 

[barry houdini]

If you can concatenate all your other data together in one cell then you should be able to use this formula to calculate the check digit

 

[indeegirl]

Excel Workbook
	A	B	C	D	E	F
1	INV_DATE	ACCT_ID	PLAN_ID	AMT_DUE	CREDIT_AMT	DEBIT_AMT
2	20110108	1167749700	8	389.78	0	-1948.9
3	20110108	1112547300	10	2048.81	0	-650.94
4	20110108	1123301200	7	-55.28	0	-649.76
5	20110108	1110464000	1	0.01	0	-612.9
6	20110108	1110464000	1	0.01	-612.9	0
Sheet1

Excel 2010


Here we go! My actual spreadsheet is larger (this wouldn't let me do the whole thing) the actual columns above are as follows:
INV_DATE = C (This will include the formula you just sent me)
ACCT_ID = K
PLAN_ID = M
AMT_DUE = W
DEBIT_AMT =AD
CREDIT_AMT = AC

A couple of other issues I am having is the PLAN_ID should be 3 digits, it is dropping the leading zeros. So it should be 008, 010, 007, 001, 001 and
the ACCT_ID does not include the last two zeros on the file above, I just typed them in. Can I fix these problems?

Is it possible to make a formula/macro for this mess? Thanks SOO much!!!!

 

[indeegirl]

Thank you! I am having a couple of other issues with the way they send me the data. See above

 

[texasalynn]

if you are importing the data, you need to mark the data as text where you have leading zeros (plan_id)

 

[texasalynn]

to concatenate the data use this

Excel Workbook
	A	B	C	D	E	F
1	INV_DATE	ACCT_ID	PLAN_ID	AMT_DUE	CREDIT_AMT	DEBIT_AMT
2	1/8/2011	1167749700	8	389.78	0	-1948.9
3	1/8/2011	1112547300	10	2048.81	0	-650.94
4	1/8/2011	1123301200	7	-55.28	0	-649.76
5	1/8/2011	1110464000	1	0.01	0	-612.9
6	1/8/2011	1110464000	1	0.01	-612.9	0
7
8
9	201101081167749700008389.780-1948.9
Sheet3

Excel 2003

Cell Formulas
Range	Formula
A9	=TEXT(A2,"yyyymmdd")&B2&TEXT(C2,"000")&D2&E2&F2