Wednesday, April 17, 2013

yogi_Extract The Latest Date Associated With Each Type of Item In Table


                                          Google Spreadsheet   Post  #1141
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr 18, 2013
user Angela R.F. :(http://productforums.google.com/forum/?zx=fdisqnjyvx1n#!category-topic/docs/spreadsheets/AeL_1gfStRQ)
Query with pivot won't display all columns
Hi, I'm trying to display the latest occurance of each task in a spreadsheet. Here's an example of the spreadsheet
Angela Books 04/15/2013
Angela Books 03/08/2013
Marylin Books 02/15/2013
Tommy Videos 04/17/2013
Tommy Videos 03/07/2013
Marylin DVDs 04/15/2013

From this spreadsheet, I'd like to display only the latest occurance of each item in column B. So, the latest row containing Books, the latest row containing Videos, and the latest row containing DVDs. For example,
Angela  Books 4/15/2013
Tommy Videos 4/17/2013
Marylin DVDs 4/15/2013

I've gotten really close with the following formula: =QUERY(A1:D6,"select max(C) pivot B")
But it doesn't include column A, the person doing the task. Anytime I try to add that in I get an error. For example, =QUERY(A1:D6,"select A, max(C) pivot B") gets the following:
error: Invalid query: Column [A] should be added to GROUP BY, removed from SELECT, or aggregated in SELECT.

Any assistance?
--------------------------------------------------------------------------------------------------------------
following is a solution to the problem