CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
xchrisbradley
xchrisbradley

Reputation: 503

GS query record based of next records value

I am trying to get the previous Approved date right before the first found Requeue date. Doing this in pure SQL is not an issue but google sheets is making this difficult. Any ideas?

ColumnA     ColumnB ColumnC
8/20/2020   Create  00310913
8/25/2020   Edit    00310913
8/26/2020   Approve 00310913
8/26/2020   Approve 00310913 <------ The `Approve` record I want
8/26/2020   Requeue 00310913 <------ First `Requeue` date
8/27/2020   Edit    00310913
8/27/2020   Approve 00310913
8/27/2020   Approve 00310913
8/27/2020   Requeue 00310913
8/27/2020   Approve 00310913
8/28/2020   Requeue 00310913
8/31/2020   Issue   00310913
9/8/2020    Close   00310913

Output

ColumnA     ColumnB ColumnC
8/26/2020   Approve 00310913

Upvotes: 1

Views: 42

Answers (2)

Kpym
Kpym

Reputation: 4033

You can use the following formula to obtain the last date of "Approve" before the first (by date) "Requeue" :

=MAX(FILTER($A:$A;$B:B="Approve";$A:$A<=MIN(FILTER($A:$A;$B:$B="Requeue"))))

And if you know that you want the line before the first "Requeue" you can do :

=INDIRECT("A"&(MATCH("Requeue";$B:$B;0)-1)&":C"&(MATCH("Requeue";$B:$B;0)-1))

Upvotes: 1

General Grievance
General Grievance

Reputation: 5033

Here's one way to do it:

=QUERY(
  ARRAY_CONSTRAIN(A:C,MATCH("Requeue",B:B,0)-1,3),
  "select Col1,Col2,Col3 
    where Col2='Approve' 
    order by Col1 desc 
    limit 1"
)
  1. We find the first occurrence of "Requeue", then return the table up to that record
  2. Then query, looking for the last occurrence of 'Approve' in Column 2.

Upvotes: 2

Related Questions