Help please! Macro to add alpha character to duplicate records

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. I'm now trying to assign an individual reference number to each course scheduled, 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. I'm needing to add this into the current VBA macros, 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$" & irow & ":A" & irow & ",A" & irow & ")+64)"

My issue now is that the macro is adding an "A" to each record, but not continuing on (so now I have three rows of LT00001A, instead of it going LT00001A, LT00001B, LT00001C).

Annotation 2021-07-07 115257.png

I really hope this makes sense, any help would be greatly appreciated!

Looks like

Please Login or Register  to view this content.

Originally Posted by hayleyzim

to assign an individual reference number to each course scheduled, such as LT00001A, LT00001B, LT00001C, etc.

What should be after the first 26 occurrences of a given Id from column "A", i.e. when the letters of the alphabet end ... and so on ?

I've just tried to run that one and now it's leaving column B empty, I'm not sure if I put it in wrong though?

I am wondering if there's a way to make it reference the cell above, if I could simply write the formula in column B I would use "=IF(A4=A3,(A4&(CHAR(CODE(RIGHT(B3,1))+1,(A4&"A"))", but I'm not sure how to recreate this in VBA.

Originally Posted by hayleyzim

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$" & irow & ":A" & irow & ",A" & irow & ")+64)"

NO,
if should be

Please Login or Register  to view this content.

And you don't need to loop, so I posted the one line code.
Add a period like

Please Login or Register  to view this content.

That worked perfectly! I've been tearing my hair out over this one, thank you so much!

No problem.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

It does thank you! I've marked it as SOLVED.

I did just think of another point I was hoping to run by you though... What if we needed more than 26 entries, is there a way to then make it continue on to LT00001AA, LT00001AB, etc? I'm not sure if we will need that many entries available, but I thought I'd check if it's possible before releasing this spreadsheet to the team.

You mean like this?

Please Login or Register  to view this content.

Is there a way to do it with this formula? It seems to be working the best with the spreadsheet.

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

If not, I'm sure we can make it work with maximum 26 entries. Being able to make it continue onto LT00001AA, LT00001AB, LT00001AC etc. would just be a bonus!

Originally Posted by hayleyzim

If not, I'm sure we can make it work with maximum 26 entries. Being able to make it continue onto LT00001AA, LT00001AB, LT00001AC etc. would just be a bonus!

That's what I posted.

It allows you to go to XFD, 16384 entries for each single unique value.