HI Experts, it's been a while, hope all of you and yours have weathered our current state as well as can be expected - we're approaching better days ahead!
I need to create a formula that calculates the check digit for 6000 17-digit SSCC numbers (20 digit number with 2 leading zeroes, dropped for purpose of manual calculation of check digit, with the check digit being the last digit, also dropped for purpose of the manual check digit calculation) - the formula used by GS1 is to take the 17 remaining digits, and multiply them as so:
Digit 1 x 3
Digit 2 x 1
Digit 3 x 3
Digit 4 x 1
Digit 5 x 3
Digit 6 x 1
Digit 7 x 3
Digit 8 x 1
Digit 9 x 3
Digit 10 x 1
Digit 11 x 3
Digit 12 x 1
Digit 13 x 3
Digit 14 x 1
Digit 15 x 3
Digit 16 x 1
Digit 17 x 3
Sum the total, then subtract it from the the nearest multiple of 10 or 100 (ex. if the sum is 101, subtract it from 110) - the single digit result of this final subtraction is the check digit.
Please see example below for what I'm trying to do - the end result I'm striving for is to create a list of 6000 SSCC numbers that I can copy and paste en masse, I need a formula in column B that executes the above and returns the 18 digit result, or, the check digit by itself, and I can join them on my own in column C. This table contains real results using the GS1 check digit calculator online, entering one SSCC, calculating, and the GS1 calculator returning the right check digit for that 17 digit sequence of characters.
17 SSCC w/o CD 18 digit SSCC w/ Check Digit
90854441001100000 908544410011000001 (so in this case, the sum total of the 17 using the formula above = 89, next 10th number up = 90, 90-89=1)
90854441001100001 908544410011000018 (100-92=8)
90854441001100002 908544410011000025 (100-95=5)
90854441001100003 908544410011000032 (100-98=2)
90854441001100004 908544410011000049 (110-101=9)
90854441001100005 908544410011000056 (110-104=6)
With my knowledge, the best I can figure for a formula would be a clunky one involving left*X, mid*X and right*X sums, it would do the trick, but hoping somebody smarter than me can provide a more streamlined formula instead?
Thanks in advance Gurus, your help is always appreciated!
GK
Bookmarks