How can I reference the previous row in the =ARRAYFORMULA function?

Question

I'm trying to create a unique ID in a Google Sheet using the =ARRAYFORMULA function. The ID should be made up by the first 3 letters of a supplier name in a column + the year(from a timestamp column) + the month(from a timestamp column) + a serial number starting with "01" and then resetting when the timestamp reaches a new month.

For the serial number to work, I need to reference the previous row. I'm not sure how to do that when using the =ARRAYFORMULA function. Any ideas will be greatly appreciated. My current attempt is as follows:

={"Purchase Order ID";ARRAYFORMULA(IF(ISBLANK(E2:E),"",left(upper(SUBSTITUTE(I2:I," ","")),3)&TEXT(E2:E,"yy")&TEXT(E2:E,"mm")&TEXT(IF(MONTH(E2:E)=MONTH(E2:E),VALUE(RIGHT(D2,2))+1,1),"00")))}

Here is a screenshot of my Google Sheet.

The desired result should look like this:

TES220601 - where the date is: 29/06/2022

TES220602 - where the date is: 29/06/2022

TEB220701 - where the date is: 11/07/2022

ZNA220702 - where the date is: 11/07/2022

MAK220703 - where the date is: 13/07/2022

KHA220704 - where the date is: 16/07/2022

ICL220705 - where the date is: 18/07/2022

HERE is a link to my Google Sheet.

Answer 1

Formula for you

={"Purchase Order ID";ARRAYFORMULA(IF(ISBLANK(E2:E),"",left(upper(SUBSTITUTE(I2:I," ","")),3)&TEXT(E2:E,"yy")&TEXT(E2:E,"mm")&TEXT(COUNTIFS(TEXT(E2:E,"mm"),TEXT(E2:E,"mm"),ROW(E2:E),"<="&ROW(E2:E)),"00")))}

Answer 2

Try below array formula-

={"Purchase Order ID";Arrayformula(IF(E2:E="",,UPPER(LEFT(SUBSTITUTE(J2:J," ",""),3))&TEXT(E2:E,"YYMM")&COUNTIFS(Month(E2:E), Month(E2:E),I2:I,I2:I, ROW(E2:E), "<="&ROW(E2:E))))}