Library Formula Sequential Numbering unique to each prefix

[Geraldine9g]

I am trying to create a column that will auto populate a document number sequentially based on the pre-fixes. I want the final numbering to look like:

FIN-FOR-001

FIN-FOR-002

FIN-PRO-001

FIN-PRO-001

HS-FOR-001

HS-FOR-002

HS-PRO-001

HS-PRO-002

I have one column called Department (i.e FIN, HS) and one column called Document Type (i.e FOR and PRO) and I am able to concatenate those two columns and use the ID as the number but this won't give me a sequential number based for each unique prefix arrangement. It will only give me the ID. 

[#2]

@Geraldine9g You cannot check the column values from other list items/rows using calculated columns. So, you cannot achieve what you are looking for using calculated column.

You will need custom solution for this, maybe using power automate flow or power apps customized forms.

---

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.