Creating Unique Batch or Lot Numbers

Greetings all

I am working on a workbook to generate batch numbers for production. The Numbers are in the form YYMxxx. A 2 digit year, a 1 letter Month (sequential alpha chars basically) and a 3 digit sequence number. File attached.

What I have is very cumbersome in that for every number generated 4 cells are written, batch#, Month, Year, & Seq. If you have to go back and enter a number after the month changes the sequence starts over duplicating a batch number which is not acceptable, and the same is true if the list gets sorted, the batch numbers could end up changing.

I would also like to simplify the entire process so that maintaining the spreadsheet would be easier. I started this several months ago, and for the life of me, the calculation in column F has a text value being multiplied by 29 and I have no idea how that calculation came to be or what it actually does other than it doesn't work without it.

There is not a problem with using VBA if that will provide a more workable solution as I am planning to create a routine to add a row at the top rather than have a finite number of rows set up with formulas that has to be added to or put into a formal table. For that matter, I don't have a problem with using a table either.

I would certainly appreciate anyone who could help with this or point me into the right direction. Right now, I am not seeing anything but problems.

Thanks!

Do away with the helper columns and the table etc..Remove all columns from F-K

Simply use..in Row 5

Please Login or Register  to view this content.

Copy down

Okay, I do not begin to understand this, but it works and I love it! I will start breaking it down to try to figure out exactly what is going on here, but for now, I can complete my project! Thanks!!

Thanks for the rep..

Here is a simple breakdown

Please Login or Register  to view this content.

gives last 2 digits of the year. Lets call this YY

Please Login or Register  to view this content.

chooses from A-W depending on the month number. Lets call this M

Please Login or Register  to view this content.

picks the rightmost 3 digits of 00 and adds one to the count of preceding YYM combinations in earlier rows