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

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

CodePudding user response：

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")))}