P
PneumaGT
Okay so far i have figured out the sticky part of the equation.
Steps 1-6..no problem. This is what i fgured out.
Step One: Suppose you want to find the Check Digit for the UCC-12
(U.P.C.) Number 61414121022. Set up a table with 12 columns, and put
the number 61414121022 into Positions One through Eleven. Position
Twelve will be blank because it is reserved for the Check Digit.
Step Two: Add the numbers in Positions One, Three, Five, Seven, Nine,
and Eleven:
Step Three: Multiply the result of Step Two by three:
Step Four: Add the numbers in Positions Two, Four, Six, Eight, and Ten:
Step Five: Add the results of Step Three and Step Four:
=SUMPRODUCT(MID(A2,{1,3,5,7,9,11},1)+0)*3+SUMPRODUCT(MID(A2,{2,4,6,8,10},1)+0)
Step Six: The Check Digit is the smallest number needed to round the
result of Step Five up to a multiple of 10. In this example, the Check
Digit is 0.
=IF(MOD(B2,10)=0,0,10-MOD(B2,10))
This gives me the 12th digit. Now here is my problem. I want to set
this up so all i have to do is put this into 1 field...such as A2 and
have the above equations work together without having to use multiple
field entries.
In addition to that i want this to put the number in the neighboring
field.
so i enter the 11 digits in Field A2 but in B2 the 12 digit number
appears.
Finally i want this to be able to apply to one entire column. So if i
put various numbers in Columns A3-A45 i want the same equation to work
on each field and put the number in the corresponding "B" Field.
Any ideas?
Steps 1-6..no problem. This is what i fgured out.
Step One: Suppose you want to find the Check Digit for the UCC-12
(U.P.C.) Number 61414121022. Set up a table with 12 columns, and put
the number 61414121022 into Positions One through Eleven. Position
Twelve will be blank because it is reserved for the Check Digit.
Step Two: Add the numbers in Positions One, Three, Five, Seven, Nine,
and Eleven:
Step Three: Multiply the result of Step Two by three:
Step Four: Add the numbers in Positions Two, Four, Six, Eight, and Ten:
Step Five: Add the results of Step Three and Step Four:
=SUMPRODUCT(MID(A2,{1,3,5,7,9,11},1)+0)*3+SUMPRODUCT(MID(A2,{2,4,6,8,10},1)+0)
Step Six: The Check Digit is the smallest number needed to round the
result of Step Five up to a multiple of 10. In this example, the Check
Digit is 0.
=IF(MOD(B2,10)=0,0,10-MOD(B2,10))
This gives me the 12th digit. Now here is my problem. I want to set
this up so all i have to do is put this into 1 field...such as A2 and
have the above equations work together without having to use multiple
field entries.
In addition to that i want this to put the number in the neighboring
field.
so i enter the 11 digits in Field A2 but in B2 the 12 digit number
appears.
Finally i want this to be able to apply to one entire column. So if i
put various numbers in Columns A3-A45 i want the same equation to work
on each field and put the number in the corresponding "B" Field.
Any ideas?