Unique Trace Code (multiple Column Criteria)

I have a challenge I haven't quite been able to figure out:

I need a 6 digit trace code to be automatically generated for each job based on the date, the product type and the job number.

The first 2 digits on the trace-code are alphanumeric and the sequence for each job is AA, AB, AC etc.

The last 4 digits are a date code (YYWW.... two digits for the year and two digits for the week number).

The criteria is that multiple jobs of the same product type within the same work week must not have identical trace codes. So Job 1 gets "AA" plus the date code... Job 2 gets "AB" plus the date code, etc. If Job 3 is done in the next work week, it cycles back to "AA" and gets the new date-code.

Generating the date-code was simple enough, but coming up with a function or rule to assign the two-digit alpha has been a challenge that I have not been able to beat.

Below is an example of how the Trace-code column should work....note that Trace code on one product type might be identical to the trace code on another product type.

A
B
C
D

1
Date
Product type
Lot number
Trace code

[Code] .........