Custom Batch Serial Number Generation

exceltrip

New Member
Joined
Jul 7, 2023
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi All,

We are a manufacturer and are trying to generate an Excel document that will automatically generate a series of sequential codes that we will use to laser etch a data matrix scan code onto a series of products made in a particular batch.

Effectively the sequential code is made up as follows;

ABBBYYMMDDCCCCSSSS​
Where;​
A = Code series, which we will start with 1 but could be alpha or numeric.​
BBB = Part detail, could be alpha, numeric or alphanumeric.​
YY = Year of manufacture, i.e. 23.​
MM = Month of manufacture, will need to be two digits such as 01 = January or 11 = November.​
DD = Day of manufacture, will need to be two digit to show 01~31.​
CCC = Manufacturing batch, could be alpha, numeric of alphanumeric.​
SSSS = Sequential series number of parts made, starting from 0001 up to 9999.​
We would like to be able to enter in all inputs for the above information A, BBB, YY, MM, DD and CCCC.

We would then like to have an input for how many parts we have made, for instance in a particular batch we made 2,000 widgets, so the input would be 2000.

We then want Excel to generate 2000 codes using the above inputs so that A, BBB, YY, MM, DD and CCCC are static and do not change, but the last four digits that make up the SSSS portion of the code sequentially go up from 0001 to 2000.

We have tried half a dozen different methods, but have not been able to solve this to date...we either get part of the code working, nothing working, or we lose the zeros in front of the SSSS number; i.e. instead of displaying 0001 we just get 1.

Can anyone shed some light on this for me please?
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
MrExcelPlayground18 (version 1).xlsb
ABC
1ItemCodeLength
2Code Series11
3Part DetailA113
4Year232
5Month072
6Day072
7BatchABC3
8Max Number20004
9
10Codes:
111A11230707ABC0001
121A11230707ABC0002
131A11230707ABC0003
141A11230707ABC0004
151A11230707ABC0005
161A11230707ABC0006
171A11230707ABC0007
181A11230707ABC0008
191A11230707ABC0009
201A11230707ABC0010
211A11230707ABC0011
221A11230707ABC0012
231A11230707ABC0013
241A11230707ABC0014
251A11230707ABC0015
261A11230707ABC0016
271A11230707ABC0017
281A11230707ABC0018
291A11230707ABC0019
Sheet18
Cell Formulas
RangeFormula
A11:A2010A11=TEXT(B2,"0")&TEXT(B3,"000")&TEXT(B4,"00")&TEXT(B5,"00")&TEXT(B6,"00")&TEXT(B7,"000")&TEXT(SEQUENCE(B8),"0000")
Dynamic array formulas.
 
Upvote 1
Solution
MrExcelPlayground18 (version 1).xlsb
ABC
1ItemCodeLength
2Code Series11
3Part DetailA113
4Year232
5Month072
6Day072
7BatchABC3
8Max Number20004
9
10Codes:
111A11230707ABC0001
121A11230707ABC0002
131A11230707ABC0003
141A11230707ABC0004
151A11230707ABC0005
161A11230707ABC0006
171A11230707ABC0007
181A11230707ABC0008
191A11230707ABC0009
201A11230707ABC0010
211A11230707ABC0011
221A11230707ABC0012
231A11230707ABC0013
241A11230707ABC0014
251A11230707ABC0015
261A11230707ABC0016
271A11230707ABC0017
281A11230707ABC0018
291A11230707ABC0019
Sheet18
Cell Formulas
RangeFormula
A11:A2010A11=TEXT(B2,"0")&TEXT(B3,"000")&TEXT(B4,"00")&TEXT(B5,"00")&TEXT(B6,"00")&TEXT(B7,"000")&TEXT(SEQUENCE(B8),"0000")
Dynamic array formulas.
That's gold...I didn't realise you could specify the number of preceding digits with the TEXT(B?,"0000") formula!
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,259
Members
452,626
Latest member
huntinghunter

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top