VBA to add alpha character to duplicate records

[hayleyzim]

Hello! I'm currently working on a spreadsheet where training is recorded. We're currently recording entries in two parts - First by training request, and then by courses scheduled. Each request is given a reference number, such as LT00001. Then each course is assigned an individual reference number, such as LT00001A, LT00001B, LT00001C, etc.
(Reason being is, we may get one training request, however there could be several different courses booked from that one request.)

The problem I'm having is that the formula I'm using only works for up to 26 duplicates (LT00001A to LT0001Z), but I'd like it to continue further (LT00001A all the way to LT0001ZZ).

I'm currently using the formula "=A4&CHAR(COUNTIF($A$4:A4,A4)+64)", which looks like this in the VBA:

.Range("B" & irow).Formula = "=A" & irow & "&CHAR(COUNTIF($A$4:A" & irow & ",A" & irow & ")+64)"

I'm not sure what to add to this formula in order to make this work, can anyone help please?

 

[Fluff]

Hi & welcome to MrExcel.
This will take you up to ZZ

=LET(Qty,COUNTIFS(A$4:A4,A4)-1,in,INT(Qty/26),A4&IF(in,CHAR(in+64),"")&CHAR(MOD(Qty,26)+65))

 

[Fluff]

A simpler solution that will work up to XFD

=LET(Col,ADDRESS(1,COUNTIFS(A$4:A4,A4),4),A4&LEFT(Col,LEN(Col)-1))

 

[hayleyzim]

A simpler solution that will work up to XFD

=LET(Col,ADDRESS(1,COUNTIFS(A$4:A4,A4),4),A4&LEFT(Col,LEN(Col)-1))

That worked perfectly, thank you so much for your help!

 

[Fluff]

You're welcome & thanks for the feedback.