Google-sheets – Single QUERY for most recents of two groups

formulasgoogle sheetsgoogle-sheets-query

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.

Best Answer

=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

Related Topic