GoogleSheets QUERY Formula Grouping

Question

Having some trouble with this sucker. Trying to use QUERY function but want to exclude Column D from grouping. Data I'm hoping to see in each respective column of QUERY table:

• Unique (non-duplicated) Names
• Most Recent Move-In Date
• Most Recent Check Date among those corresponding to "Most Recent Move-In Date" for each unique name
• Check Amount Corresponding to that "Most Recent Check Date"

=QUERY(
  A:D,
  "select A,B, max(C), D where not B is null group by A,B,D label A 'Client Name',
    B 'Move-In Date',max(C) 'Check Date',D 'Amount'"
)

What I've figured out so far is that including "Column D" in "group by" causes duplicate Names to appear, but without including that column in "group by" I get a "#VALUE!" error.

See link for sample data with examples: Data Test

Answer 1

Try the following on a copy of your demo spreadsheet.

1. On G15 add the following formula

=QUERY(
   A:D,
   "select A,max(B), max(C) where not B is null group by A label A 'Client Name',
     max(B) 'Move-In Date',max(C) 'Check Date'"
 )

2. On J15 write Amount
3. On J16 add the following formula

=ArrayFormula(vlookup(G16:G19&I16:I19,{A2:A11&C2:C11,D2:D11},2,0))