Creating a reference from data in two cells and individually numbering

Attached is a spreadsheet

To create the required reference, I need to add the whole of the text from column A to the last three characters of the text from column B.

Therefore, data from cells A2 and B2 become ABP2122DEF.

However, there are instances were the last three characters of the references to be generated are the same and so an additional numerical character starting from 1 should be added to the end of the reference, therefore reference from cells A2 and B2 becomes ABP2122DEF1.

The numerical order should be generated based on the alphabetical order of the third character of the data in column A.

e.g.
A2 = ABP2122 - last character of reference becomes 1 - complete reference is ABP2122DEF1
A3 = ABQ2122 - last character of reference becomes 2 - complete reference is ABQ2122DEF2
A4 = ABR2122 - last character of reference becomes 3 - complete reference is ABR2122DEF3

The expected reference numbers are shown in column E.

Not sure if this generation of reference numbers can be achieved in the required format. Thanks for viewing.

Thank you for the quick response.

The formula works perfectly

If there is no data in the field, how can I stop the calculation returning zero (0), but leaving the field as blank?

Is there anyway that the last duplicate in column B can be highlighted in RED (for example) to indicate the last duplicate in the range? Columns A and B on the row should be highlighted to indicate the last reference

Attached is a file where column B creates a reference from columns C & D.

Firstly, when there is no data in columns C & D, the calculation returns '0'. Can this sum be modified so that blank is returned instead of '0'?

Secondly, the references calculated in column B end numerically. Can the adjacent cells in columns C & D be highlighted at the end of each numerical sequence as demonstrated in the spreadsheet?

Originally Posted by Sam Capricci

try this instead... =IF(OR(C2="",D2=""),"",C2&RIGHT(D2,3)&COUNTIF($D$2:D2,D2))
dragged down.

Thanks Sam 😀
Is there anyway where the last numerical sequence in column B can highlight the adjacent data in cells of columns C & D?