1

I have a table with 3 columns. I want to filter only the highest value of column B for each date (A) if column C contains the code "red".

My sheet:

date    entries code
08/04/2019  1   red
08/04/2019  3   red
07/04/2019  2   blue
07/04/2019  1   green
06/04/2019  6   red
06/04/2019  5   red
06/04/2019  4   blue

My query so far:

=QUERY(A2:C7, "select A, B where C = 'red'", 1)

My result:

08/04/2019  1
08/04/2019  3
06/04/2019  6
06/04/2019  5

But I would like to get only the highest value of each date:

08/04/2019  3
06/04/2019  6

I can even filter the highest value of B based on the dates of A (see below), but I can not filter the C code together.

=QUERY(A2:C7, "select A, max(B) group by A")

My question is: How to filter only the highest value of column B for each date (A) if column C contains the code "red"?

0

=ARRAYFORMULA(UNIQUE(VLOOKUP(
 QUERY(A2:C, "select A    where C = 'red' order by A desc, B desc", 0), 
 QUERY(A2:C, "select A, B where C = 'red' order by A desc, B desc", 0), {1,2}, 0)))

0

  • 1
    Wow!! Exactly what I needed. Thank you very much for your help, my friend. – rdllngr Apr 8 at 16:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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