0

I have two sheets with the same columns. I need a single query to pull the most recent matching record from each of the two sheets. Here I'll just show them as adjacent ranges:

| Stu   | Date          | Type      | Comment           |
|------ |------------   |---------  |-----------------  |
| Bill  | 28/03/2019    | Reading   | good job          |
| Rick  | 26/03/2019    | Writing   | bad job           |
| Bill  | 14/03/2019    | Reading   | ok job            |
| Rick  | 15/03/2019    | Writing   | soso job          |
| Rick  | 16/03/2019    | Reading   | not good at all   |
| Bill  | 17/03/2019    | Writing   | please try!       |

I'd like to create a single query that gives me the most recent of each type (1 reading and 1 writing) for each student. (So in this example, Bill's most recent reading and writing comment in the first two rows, and Rick's most recent reading and writing in the next two rows.)

I've tried a few combos of Group and Limit and Max that almost worked but not quite.

0

=QUERY({
 QUERY(QUERY(A2:D, "order by B desc"), "where Col1='Bill' and Col3='Reading' limit 1");
 QUERY(QUERY(A2:D, "order by B desc"), "where Col1='Bill' and Col3='Writing' limit 1");
 QUERY(QUERY(A2:D, "order by B desc"), "where Col1='Rick' and Col3='Reading' limit 1");
 QUERY(QUERY(A2:D, "order by B desc"), "where Col1='Rick' and Col3='Writing' limit 1")},
 "order by Col2 desc")

0


  • to account for possible errors:

=QUERY({
 IFERROR(QUERY(QUERY({A2:D},"order by Col2 desc"),
                            "where Col1='Bill' and Col3='Reading' limit 1"),{"","","",""});
 IFERROR(QUERY(QUERY({A2:D},"order by Col2 desc"),
                            "where Col1='Bill' and Col3='Writing' limit 1"),{"","","",""});
 IFERROR(QUERY(QUERY({A2:D},"order by Col2 desc"),
                            "where Col1='Rick' and Col3='Reading' limit 1"),{"","","",""});
 IFERROR(QUERY(QUERY({A2:D},"order by Col2 desc"),
                            "where Col1='Rick' and Col3='Writing' limit 1"),{"","","",""})},
 "where Col1 is not null order by Col2 desc")

0

  • That's similar to what I'd come up with, but I've learned some different syntax from this example. Unfortunately, it doesn't quite work as I need because this example requires hardcoding the student names. – user2767078 Apr 16 at 8:30
  • @user2767078 webapps.stackexchange.com/help/someone-answers – user0 Apr 16 at 8:57

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.