0

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
data at start

After New Row Added
preferred result

2 Answers 2

1

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.
2
  • 1
    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.
    – Blindspots
    Apr 12 at 17:50
0

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().

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.