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

Rubén Rubén · Accepted Answer · 2016-10-20T15:37:57

Try the following on a copy of your demo spreadsheet.

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'"
 )

On J15 write Amount
On J16 add the following formula

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

GoogleSheets QUERY Formula Grouping

1 Answers