Sequence by group or batch

[Pints]

Hi
Long time reader, first time poster.

In a journal template, a user can enter any number c. 350 predefined 4 digit codes. Each code has to be used at least twice (such is the nature of balancing journals). For each code that the user selects, I need the template to produce a new, sequential, batch ID.
For example, code 1234 might be used 5 times and each of those should use the ID 01. For code 4321, the ID used would need to use ID 02. For another code, ID 03 would be used.

There's no specific requirement that the codes are grouped together by the user (e.g. the list could be 1234, 1234, 4321, 1234)

How can I use either a formula or VBA to produce a sequential ID for each "batch" of codes? e.g. 1234 = 01, 4321 = 02, etc.

P.S. The ID produced will ultimately be used when populating an output file with VBA for loading to the accounting package.
P.P.S. If it makes any difference, not all users will be using Office 365 when populating the template.

Thanks,
Pints

 

[Eric W]

Welcome to the forum!

How about:

Book1
	A	B
1	Code	ID
2	1234	1
3	1234	1
4	4321	2
5	1234	1
6	4321	2
7	2345	3
8	3456	4
9	2345	3
10	1234	1
11	4321	2
12	3456	4
13	5555	5
14	5555	5
15		#N/A
Sheet10

Cell Formulas
Range	Formula
B2:B15	B2	=IF(COUNTIF(A$2:A2,A2)=1,MAX(B$1:B1)+1,INDEX(B$2:B2,MATCH(A2,A$2:A2,0)))

If you don't want #N/A to show up, we can add an IFERROR:

=IFERROR(IF(COUNTIF(A$2:A2,A2)=1,MAX(B$1:B1)+1,INDEX(B$2:B2,MATCH(A2,A$2:A2,0))),"")

although that's not available in older versions of Excel. If you want the ID number to have preceding zeros, you can use TEXT or use a custom format on those cells.

 

[Pints]

By jove, I think you've got it! Thanks.

 

[Eric W]

Glad it works for you! Thanks for the feedback. ?

 

[Pints]

Re. the preceding zeroes, I'll add that formatting into the VBA for when it creates the output file.