Change of value based on subsequent change of status in following rows

[noman999]

Dear Gurus
Need your support for data having 2 columns - 1st column having unique document value and 2nd column having current status. Document number is repeated in subsequent rows due to subsequent change of status.

Now I need a formula to have final status as "open" or "closed" in column 3 based on 2nd column.

For example -
Column 1 - Document number "12" - Row 1 (and repeated subsequently on Row 5)
Column 2 - Current status "At Head Office" - Row 1 (and "Settled" at Row 5 corresponding same document number"
Expected result in column 3-

Formula to come up with result (say Result 1) "Open" in column 3 (while completing column 1 and 2 in 1st row)
as soon as same document number (in column 1) repeated in Row 5 (as per example above) with status "settled" (in column 2 - Row 5), result (result 2) to be "close" in 3rd column for same row and simultaneously change result (result 3) the earlier row status from "open" to "close" as well (in column3 - Row 1) .
(Result 1 and 2 is easy to get but need support on result 3 to change the result of earlier cell as well)

I thought some sort of Array formula like below but it is not working:

{=IF(Column 2="Cash Settled","Closed",IF(SUMPRODUCT($Column 1=Column 1,$

Column 2​

="Cash Settled"),"Closed","Open"))}