The formula has to be input on cell A2. The logic should be that the formula would result in the following cell (from cell A3 downwards) outputting a no. in a reversed numbered list format in column 1. And for those who are "Resigned" or "Dismissed" in column 3, the formula would skip them and the next numbering would be a follow-up from the previous no. instead.
We're using in-house software that's similar to Google Sheets and Microsoft Excel so certain functions/formulas like REGEX and custom functions are not supported.
| No. | Name | Employment status |
|---|---|---|
| (insert formula here | Sample Name | Type of Employment |
| 5 | John | Full-time |
| Mary | Resigned | |
| 4 | Jack | Part-time |
| 3 | Tim | Contract |
| Jane | Dismissed | |
| 2 | Jonathan | Full-time |
| 1 | Larry | Part-time |
This post is a repost from this ask as the solutions weren't applicable to this issue, unfortunately. So I'm looking for alternative solutions from those answers.
Alternative:
=ARRAYFORMULA(IF(MMULT(N(C2:C8={"Resigned","Dismissed"}),{1;1}),"",MMULT(N(ROW(C2:C8)<=TRANSPOSE(ROW(C2:C8))),1-ISNUMBER(MATCH(C2:C8,{"Resigned","Dismissed"},0)))))
Note that the oft-seen technique of replacing the upper cell reference in the range passed with a reference to the entire column (for example, C2:C) would here not be advisable. If such flexibility is required, suggest you create a dynamic Named Range.