Hello,
I'm trying to put together a excel workbook that will calculate the code for a barcode. The barcode is "almost" a HIBC barcode (HERE). However someone decided to "alter" the standard (specifically how it decides to put a $, $$, or none before the second concatenated code) and the generator tools just don't work. So here are the 9 individual fields that go into the barcode;
[TABLE="class: grid, width: 800, align: center"]
<tbody>[TR]
[TD="align: center"]Field[/TD]
[TD="align: center"]Value[/TD]
[TD="align: center"]Status/Description[/TD]
[/TR]
[TR]
[TD]ID Mark for Standard[/TD]
[TD="align: center"]+[/TD]
[TD]Simple, don't need help.[/TD]
[/TR]
[TR]
[TD]Label/Location Identification Code (LIC)[/TD]
[TD="align: center"]<user input=""></user>[/TD]
[TD]Simple, don't need help.[/TD]
[/TR]
[TR]
[TD]Product/Catalog Number[/TD]
[TD="align: center"]<user input=""></user>[/TD]
[TD]Simple, don't need help.[/TD]
[/TR]
[TR]
[TD]Unit of Measure[/TD]
[TD="align: center"]<user input=""></user>[/TD]
[TD]Simple, don't need help.[/TD]
[/TR]
[TR]
[TD]Separator[/TD]
[TD="align: center"]/[/TD]
[TD]Simple, don't need help.[/TD]
[/TR]
[TR]
[TD]Expiration Date[/TD]
[TD="align: center"]<user input=""></user>[/TD]
[TD]I want users to enter dates normally (MM/DD/YYYY) but it needs to be changed to a 5 digit julian date. I'm using a formula to do this which works. So I think I have this.[/TD]
[/TR]
[TR]
[TD]Lot Number[/TD]
[TD="align: center"]<user input=""></user>[/TD]
[TD]Simple, don't need help.[/TD]
[/TR]
[TR]
[TD]Date of Manufacturing[/TD]
[TD="align: center"]<user input=""></user>[/TD]
[TD]Need help. This is formatted as 3 characters. First is the month represented by a letter (Jan=A, Feb=B,..Dec=L). last two digits are for the year (A15 = Jan 2015).[/TD]
[/TR]
[TR]
[TD]Modulus 43 Check Character[/TD]
[TD="align: center"]<calculated></calculated>[/TD]
[TD]Need help. This is calculated from the SUM of the whole barcode string. However for non-number values there is a correlating value and it isn't the ANSI equivalent. That info is HERE. Then the total is divided by 43 and you take the remainder (MOD).[/TD]
[/TR]
</tbody>[/TABLE]
Once I have all the values calculated I can just concatenate them with the "&" operation. Really there are two difficult fields.
What I was thinking could be done for the date of manufacturing would be to create a lookup table or some type of custom mapping. I swear I've seen this done with excel before, where you could basically define 01 or January to equate to the letter A. Unfortunately the date formatting didn't do this it does a 3 character date format where it takes the first character of the Month (J15 = January 2015).
With the Modulus 43 value it sounded easy but I can't find a good way to do it in excel. In a programming language like Java or C++ I would store the values in an array, step through them, have a case statement that said 'if array= "$" then +39' and so on for each possibility (or create some form of map). However I'm not sure if there is a way to accomplish this using excel functions to basically say "step through this array of characters, compare it to this lookup table, and sum the values".
Once I have the code correct creating the barcode image can be done.
Thanks for any assistance.
I'm trying to put together a excel workbook that will calculate the code for a barcode. The barcode is "almost" a HIBC barcode (HERE). However someone decided to "alter" the standard (specifically how it decides to put a $, $$, or none before the second concatenated code) and the generator tools just don't work. So here are the 9 individual fields that go into the barcode;
[TABLE="class: grid, width: 800, align: center"]
<tbody>[TR]
[TD="align: center"]Field[/TD]
[TD="align: center"]Value[/TD]
[TD="align: center"]Status/Description[/TD]
[/TR]
[TR]
[TD]ID Mark for Standard[/TD]
[TD="align: center"]+[/TD]
[TD]Simple, don't need help.[/TD]
[/TR]
[TR]
[TD]Label/Location Identification Code (LIC)[/TD]
[TD="align: center"]<user input=""></user>[/TD]
[TD]Simple, don't need help.[/TD]
[/TR]
[TR]
[TD]Product/Catalog Number[/TD]
[TD="align: center"]<user input=""></user>[/TD]
[TD]Simple, don't need help.[/TD]
[/TR]
[TR]
[TD]Unit of Measure[/TD]
[TD="align: center"]<user input=""></user>[/TD]
[TD]Simple, don't need help.[/TD]
[/TR]
[TR]
[TD]Separator[/TD]
[TD="align: center"]/[/TD]
[TD]Simple, don't need help.[/TD]
[/TR]
[TR]
[TD]Expiration Date[/TD]
[TD="align: center"]<user input=""></user>[/TD]
[TD]I want users to enter dates normally (MM/DD/YYYY) but it needs to be changed to a 5 digit julian date. I'm using a formula to do this which works. So I think I have this.[/TD]
[/TR]
[TR]
[TD]Lot Number[/TD]
[TD="align: center"]<user input=""></user>[/TD]
[TD]Simple, don't need help.[/TD]
[/TR]
[TR]
[TD]Date of Manufacturing[/TD]
[TD="align: center"]<user input=""></user>[/TD]
[TD]Need help. This is formatted as 3 characters. First is the month represented by a letter (Jan=A, Feb=B,..Dec=L). last two digits are for the year (A15 = Jan 2015).[/TD]
[/TR]
[TR]
[TD]Modulus 43 Check Character[/TD]
[TD="align: center"]<calculated></calculated>[/TD]
[TD]Need help. This is calculated from the SUM of the whole barcode string. However for non-number values there is a correlating value and it isn't the ANSI equivalent. That info is HERE. Then the total is divided by 43 and you take the remainder (MOD).[/TD]
[/TR]
</tbody>[/TABLE]
Once I have all the values calculated I can just concatenate them with the "&" operation. Really there are two difficult fields.
What I was thinking could be done for the date of manufacturing would be to create a lookup table or some type of custom mapping. I swear I've seen this done with excel before, where you could basically define 01 or January to equate to the letter A. Unfortunately the date formatting didn't do this it does a 3 character date format where it takes the first character of the Month (J15 = January 2015).
With the Modulus 43 value it sounded easy but I can't find a good way to do it in excel. In a programming language like Java or C++ I would store the values in an array, step through them, have a case statement that said 'if array= "$" then +39' and so on for each possibility (or create some form of map). However I'm not sure if there is a way to accomplish this using excel functions to basically say "step through this array of characters, compare it to this lookup table, and sum the values".
Once I have the code correct creating the barcode image can be done.
Thanks for any assistance.