How do you flag cells by comparing values in another column with the same 'ID' column?-CodePudding

I have a sample table like this:

base

I wanted to flag if there is an increase in Output of the same ID compared to the previous Date. Note that the data is only sorted by Date.

Expected output: output

I've been trying to find the correct formula so that it only compares between current output with previous output of the same ID in previous date with INDEX-MATCH functions and had no luck. Thank you for your assistance.

=IF(AND(D2>INDEX($D$2:$D$9, MATCH(C2,$C$2:$C$9,0)-1), C2=INDEX($C$2:$C$9, MATCH(B2,$B$2:$B$9,0)-1)), "Flag", "")

CodePudding user response：

Try the following formula-

=IF(C2>TAKE(TAKE(FILTER($C$2:$C2,$B$2:$B2=B2,0),-2),1),"Flag","")

CodePudding user response：

The following is an array approach. In cell D2 use the following formula:

=LET(A, A2:A9, B, B2:B9, C, C2:C9, MAP(A, B,C, LAMBDA(aa,bb,cc, LET(
  f, FILTER(C, (B=bb) * (A < aa),""), IF(OR(@f="", TAKE(f,-1)>=cc),"", "Flag")))))

Here is the output:

On each MAP iteration we filter Output column (C) by ID (B) equals to bb and only previous dates. To select filtered the output (f) that corresponds to previous date we use: TAKE(f,-1), i.e. select the last filtered row (data is sorted by date in ascending order). Then we check for empty condition: the filter result is empty (@f=""), i.e. no result returned under filter condition or the output is not ascending.