I wanted to set the maximum value of each unique input as active

In the example data below, for each unique `SYS Name`, the `Status` column is marked `Active` in the row with that `SYS Name`'s highest `Serial Number`.

In Status, Column C, for each unique Sys Name the row with the highest Serial Number is labeled `Active`

1. In the following table, `C2` and `C5` are marked active because

• `B2` is highest `Serial Number` for `SYS 1`
• `B3` is the highest one for `SYS 2`
A B C
1 SYS Name Serial Number Status
2 SYS 1 1 `Active`
3 SYS 2 1
4 SYS 2 2
5 SYS 2 3 `Active`
6
2. When a new row of data is added for `SYS 1` in row 6, `B2` is no longer its highest Serial Number and the `Active` switches to `C6`:

A B C
1 SYS Name Serial Number Status
2 SYS 1 1
3 SYS 2 1
4 SYS 2 2
5 SYS 2 3 `Active`
6 SYS 1 2 `Active`

I need help creating an array formula in column C that will keep track of the current active row.

Starting Data

Put the following formula in `C1` (headings row) so that your data and any manipulation don't interfere with it:

``````={"Status";
LET(rng,A2:B,
ARRAYFORMULA(
IF(
COUNTIF(
VLOOKUP(
UNIQUE(TOCOL(INDEX(rng,,1))),
SORT({rng,ROW(rng)},2,0),3,0),
ROW(rng)),
"Active",)))}
``````
• This solution assumes that the serial numbers for each `Sys #` are unique per the example data in the question.
• If there are duplicate serial numbers for the same `Sys #` the first result will be tagged based on the current sort order.
• Note that in the event the serial numbers are not unique per `SYS name`, the formula will only show `Active` for the first instance of the max serial number. Apr 12 at 11:07
• @doubleunary that is correct. The approach was based on the example data and my interpretation of the user's requirements they explicitly stated "unique input". I have edited the answer to make those assumptions explicit. It would be peculiar to have duplicate serial numbers in any case. Apr 12 at 17:50

You can do that with `map()`, `ifs()` and `filter()`, like this:

``````=ifna(map(A1:A, B1:B, lambda(a, b, ifs(
row(a) = 1, "Status",
b = "", iferror(ø),
b = max(filter(B1:B, A1:A = a)), "Active"
))))
``````

See map(), ifs() and filter().