1

I have a sample table like this:

base

1

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

2

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", "")
vimuth
  • 5,064
  • 33
  • 79
  • 116
babooshka
  • 13
  • 3

2 Answers2

1

Try the following formula-

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

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
  • That looked great. Could you please elaborate the logic behind that expression? I'm trying to find a workaround without using dynamic array functions like TAKE and FILTER. thankyou for your assistance nonetheless. – babooshka Jan 04 '23 at 04:13
  • `FILTER()` function will filter output based on `ID`. First `TAKE` function with parameter `-2` will keep last 2 output. Then `TAKE` with 1 will keep second last output to compare with current output (all are based on ID). IF function will detect either it increase or not. – Harun24hr Jan 04 '23 at 04:21
  • If you want to avoid dynamic formulas then could search for `AGGREGATE()` function. – Harun24hr Jan 04 '23 at 04:22
  • Thanks for keeping it simple, I'll check on AGGREGATE() function soon. – babooshka Jan 04 '23 at 04:30
0

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: excel 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.

David Leal
  • 6,373
  • 4
  • 29
  • 56