Check Digit Generation Formula

[hemeraser]

Working on generating a few thousand SSCC codes.

The code is 17 digits plus a check digit. For instance 00813590020000000 should return 008135900200000002.

The check digit is calculated by this process:

I found a formula on these forums that calculates 13 digit codes but have came up short using Google to find a formula for what I need.
The formulas I found are:

=A2&(10 -MOD(SUMPRODUCT( MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*((MOD(ROW(INDIRECT("1:"&LEN(A2))),2) =1) +3*(MOD(ROW(INDIRECT("1:"&LEN(A2))),2) =0))),10))

Any help would be appreciated.

 

[oldbrewer]

	0	0	8	1	3	5	9	0	0	2	0	0	0	0	0	0	0
	3	1	3	1	3	1	3	1	3	1	3	1	3	1	3	1	3
68	0	0	24	1	9	5	27	0	0	2	0	0	0	0	0	0	0
5
73
7
70
2

<colgroup><col span="15"><col span="2"><col></colgroup><tbody>
</tbody>

 

[hemeraser]

What is this formula, I'm confused, thank you

 

[barry houdini]

Hello hemeraser, welcome to MrExcel

If your codes always have 17 digits you could use a formula tailored to that specific number, e.g in your case

=A2&MOD(SUMPRODUCT({3;1;3;1;3;1;3;1;3;1;3;1;3;1;3;1;3},-MID(A2,ROW(INDIRECT("1:17")),1)),10)

 

[hemeraser]

I get this when using the formula

 

[barry houdini]

Sorry, I edited my formula but too slow! The separators between the 3s and 1s need to be semi-colons rather than commas, as my revised formula now shows....

 

[hemeraser]

Thank you So much

 

[hemeraser]

Now say that if I wanted to make a column of numbers incremented for say 2000 times how would I go about that since excel wont handle numbers that long, only text, I'm confused as to how to make an incremented list of number that are text so I can run the formula on them in the second column. Here is how I had it:

I was using the first column as a text 00 and running the increment in the second column to generate the list, what I need is to just generate a list of the 17 digit numbers with the leading zeroes there all as text so i can run the formula.

 

[Scott Huish]

Your answer will be text, but

="00"&813590020000000+(ROWS($A$1:A1)-1)

Copy down.

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:225px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >B</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">00813590020000000</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">00813590020000001</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">00813590020000002</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">00813590020000003</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">00813590020000004</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">00813590020000005</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B10</td><td >="00"&813590020000000+(ROWS<span style=' color:008000; '>($A$1:A1)</span>-1)</td></tr><tr><td >B11</td><td >="00"&813590020000000+(ROWS<span style=' color:008000; '>($A$1:A2)</span>-1)</td></tr><tr><td >B12</td><td >="00"&813590020000000+(ROWS<span style=' color:008000; '>($A$1:A3)</span>-1)</td></tr><tr><td >B13</td><td >="00"&813590020000000+(ROWS<span style=' color:008000; '>($A$1:A4)</span>-1)</td></tr><tr><td >B14</td><td >="00"&813590020000000+(ROWS<span style=' color:008000; '>($A$1:A5)</span>-1)</td></tr><tr><td >B15</td><td >="00"&813590020000000+(ROWS<span style=' color:008000; '>($A$1:A6)</span>-1)</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span>

 

[hemeraser]

Thank you guys for all your help, boss is happy. I pretty much just used concatenate to make the two cells into one and copied and special pasted the values and ran the check digit formula, it came out nice.