Formulas to create a numbering based on other cells values

The formula in Z is looking to the formula in X and generates the unique identifier based on the values in columns B, J, and V. Here's a breakdown of the formula:

IF(V5<>"", ...) checks if the value in cell V5 is not empty.
If the condition is true (V5 is not empty), the formula continues to the inner IF statement.
IF(B5="", "", CONCATENATE("CTRI-", VLOOKUP(K5, Clients, 2, FALSE), TEXT(ISOWEEKNUM(J5), "00"), TEXT(ROW($B$4) - ROW(B5), "000"))) checks if the value in cell B5 is empty.
If the condition is true (B5 is empty), it returns an empty string.
If the condition is false (B5 is not empty), the CONCATENATE function is used to create the unique identifier.
"CTRI-" is a constant string used as a prefix for the identifier.
VLOOKUP(K5, Clients, 2, FALSE) performs a VLOOKUP function using the value in cell K5 as the search key. It searches for a matching value in the "Clients" range and retrieves the corresponding value from the second column.
TEXT(ISOWEEKNUM(J5), "00") extracts the ISO week number from the date in cell J5 using the ISOWEEKNUM function and formats it as a two-digit number.
TEXT(ROW($B$4) - ROW(B5), "000") calculates the row number difference between the current row and the fourth row in column B. It formats the difference as a three-digit number.
If any error occurs during the evaluation of the inner IF statement, the IFERROR function returns an empty string.
If the condition in the outer IF statement (V5 is not empty) is false, the entire formula returns an empty string.
Therefore, the formula in Z5 is dependent on the formula in X5, as it uses the same logic to generate the unique identifier based on the conditions and values in the corresponding rows.


What I want is that the formula in z, after looking to X and found no repetition to continue with the next numbering 002,003,004

Originally Posted by JohnTopley

If the above is based on sheet"Register", then you need check your columns e.g. Column K is "Type of Waste" / colummn J are not dates ....

John unfortunately the file is 2MB and is failing upload 😢😢

Originally Posted by JohnTopley

If the above is based on sheet"Register", then you need check your columns e.g. Column K is "Type of Waste" / colummn J are not dates ....

And you are right the files I sent it wrong.

John...do you have another way I can send you the files

I have attached screenshots

C5 has formula "=K5&J5&E5"
F5 has formulas "=IFERROR(IF(B5="","",CONCATENATE((TEXT(WEEKNUM(B5),"00")),(VLOOKUP(K5,Clients,2,FALSE)),(TEXT(ROW($B$4)-ROW(B5),"000")))),"")"
I5 has formulas: "=IF(OR(K5="",H5=""),"",(K5&H5))"
V5 has formulas: "=IF(U5="","",IF(U5=0%,"Não iniciado",IF(U5<100%,"Em progresso",IF(U5=100%,"Concluido",IF(U5="Em Espera","Pendente",IF(U5="Cancelado","Cancelado",""))))))"
X5 has formulas: "=IF(V5<>"",IFERROR(IF(B5="","",CONCATENATE("CTRI-",(VLOOKUP(K5,Clients,2,FALSE)),(TEXT(ISOWEEKNUM(J5),"00")),(TEXT(ROW($B$4)-ROW(B5),"000")))),""),"")"
Z5 has formulas: "=IF(U5<>100%,"",IF(X5="","",IF(AND(COUNTIF(K$4:K5,K5)>1,COUNTIF(H$4:H5,H5)>1),VLOOKUP(I5,$I$5:$X$2000,16,FALSE),X5)))"

Here we go ...I have been able to send the files.
What I need to achieve is to have the formulas on column Z to have the last 3 digits subsequent

Número do Certificado
CTRI-1202-001 ---ok
CTRI-1202-001 ---ok
CTRI-1202-001 ---ok
CTRI-1202-001 ---ok
CTRI-1202-001 ---ok
Empty - The formula should reserve 002
CTRI-3107-007 --- This one should be 003
CTRI-1205-008 --- This one should be 004
CTRI-1205-008 --- This one should be 004
CTRI-1205-008 --- This one should be 004
CTRI-1205-008 --- This one should be 004
CTRI-1205-008 --- This one should be 004
CTRI-1205-008 --- This one should be 004
CTRI-1206-014 --- This one should be 005
CTRI-1206-014 --- This one should be 005
CTRI-1206-014 --- This one should be 005
CTRI-1206-014 --- This one should be 005
CTRI-1206-014 --- This one should be 005
CTRI-1206-014 --- This one should be 005
CTRI-1206-014 --- This one should be 005
CTRI-1206-014 --- This one should be 005
CTRI-1206-014 --- This one should be 005
CTRI-3107-007 --- This one should be 003
CTRI-1215-024 --- This one should be 006

Unfortunately my excel doesn't have function "UNIQUE" . ..I'm still using an old version

Thank you @hansDouwe for your response - I tried to change the values for fews cells to see if the formulas works but it seems not to work.
The idea here is to have the same certificate number when the client name (column K) and the PO number (Column H) are the same.

Glen, you formulas has to be reviewed because if you change the client name for an existing lines, it doesnt change the certificate number (column Z)
Please do a test by changing the client name (column K) and the PO number (column H) for e.g lines 8 and 9 - THE Final result (column Z) gives CTRI-1502-002.
However we had already a certificate with end digit "002" which become a 003. the idea here is to avoid a given certificate number to change as we manipulate the others data.