yogi_Create Pivot Table For Selected Clients From Raw Data

Google Spreadsheet   Post  #2085

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov-20-2016

question by: Wynne Walker:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!msg/docs/aAI6LrJ0Q88/NBmAIbqAAgAJ;context-place=topic/docs/8ogMiVvgF2I
Help with query and pivot

Well, I tried for over hour and gave up on this. I am new to query formulas and pivots.

I have a tab with the columns: Client, Date, Actual Hours. This is imported from a bunch of raw data from our time tracking software.

I have a 2nd tab with the columns: Client, Target Hours. The data in these 2 columns is manually entered.

I want a 3rd tab to have the columns: Client from tab 2, Target Hours from tab 2, Actual Hours by Month (multiple columns for each month from tab 1), Total of all months.  See below and attachment. 

Client          Target Hours           Jan    Feb    March   Apr  .....   Total

John Doe           2                         .5      1         3          2              30

Jane Doe         etc ......

On the attachment its using a pivot table, which is formatting it EXACTLY how I want BUT its not filtering out the clients we've deleted by manually entering them on tab 2. So imagine we have 100 clients but we only care about tracking our time on 50 of them. Thats what the 2nd tab is for, its a manually selected list of clients we care about and we want the 3rd tab, time by month, to only show the 50.

I tried the following query which works for one row, John Doe (A2) but I dont know how to make it continue down the rows by having A2 increment to B2, C2, etc. 

=query('RAW Data Time'!A:AA,"select AA, sum(K) where AA ='"&A2&"' group by AA pivot A")

Sorry if this is super confusing!!

Thanks!

Screenshot 2016-11-20 11.04.29.png

175 KB

Categories:ChromeAsk a "