Search code examples
google-sheetsworksheet-function

Pivoting programatically and carrying color information in summary of Google sheets

We have a base table that looks like this, in our main sheet called "MainData".

We would like to summarize it in a new worksheet. The summary needs to be by time, in a program management mode, where the "When" becomes the main view, in the following way. We could technically get a version of the top part of the table via Pivot, but that forces a new worksheet. We would like this entire view to be in our own second sheet of choice which we can call "Summary".

Not sure where to begin with this. The GETPIVOTDATA command seems a more convenient way to control how the pivot shows without forcing a worksheet, but it's the itemization of colours etc that is confusing. In each week's listing below that week column, we'd like to show the items but their cell needs to be coloured by the Status that item is in.

Not looking for ready made solutions (although I won't revolt if that's shared), just looking for pointers for which functions to look for. Thanks muchly!

Solution

  • Solution:

    It's quite a task... :) I have build working solution for you.

    Go to this link to grab this (2 sheets - data and report)

    Explanation:

    Data sheet:

    I added an extra column to source data - we will need this column in further query (you can hide this column)

    ={"Rep Desc";ArrayFormula(if(A2:A<>"";"Count of "&A2:A;))}

    enter image description here

    Report sheet:

    I added 2 extra columns (A:B) (you can hide them later) to explain better what is going on. There are 4 main parts to this solution - you are able to pack all of them into one formula, but for sake of clarification I left them separate.

    Part 1 Numbers of "Open / Closes / Attn"

    This is simple query - we use extra column in data source to have desire description (Count of... instead just Attn, Closed, etc)

    =QUERY({INDIRECT($A$1)};$B$1;1)

    string to query

    select Col5, count(Col4) where Col1 is not null group by Col5 pivot Col3 label Col5 ''

    enter image description here

    Part 2 - "Sum of Point"

    Its Query again put into next query to remove headers + "Sum of Points" as an extra column (using inline array - {}):

    ={"Sum of Points"\QUERY(QUERY({INDIRECT($A$1)};B5;1);"select * offset 1";0)}

    string to query

    select sum(Col4) where Col1 is not null pivot Col3

    enter image description here

    Part 3 - "Features"

    It is quite complicated... If I find more time I will describe what is going on here... but for now just code:

      =QUERY(

transpose(ArrayFormula(SPLIT(
transpose(SPLIT(
TEXTJOIN("^";1;transpose(
{SPLIT(join(" @@ ";transpose(query(transpose(QUERY({INDIRECT($A$1)};$B$9;1));"select Col1 offset 1";0)));" @";0;1);
QUERY(ArrayFormula(IF(TRANSPOSE(query(transpose(QUERY({INDIRECT($A$1)};$B$9;1));"select * offset 1";0))<>"";
query(QUERY({INDIRECT($A$1)};$B$9;1);"select Col1";0);""));"select * offset 1";0)}
))
;"@ ";0;1))
;"^")))
;
"select * offset 1";0)

    enter image description here

    Part 4 - Conditional formatting

    For range D9:H apply 3 rules with corresponding color :

    =INDEX(INDIRECT("data!$A:$A");MATCH(D9;INDIRECT("data!$B:$B");0);1)="Open"

=INDEX(INDIRECT("data!$A:$A");MATCH(D9;INDIRECT("data!$B:$B");0);1)="Closed"

=INDEX(INDIRECT("data!$A:$A");MATCH(D9;INDIRECT("data!$B:$B");0);1)="Attn"

    enter image description here

    OK?

    Is that what you were going to achieve?

    Again - this is working copy for you:

    Go to this link to grab this (2 sheets - data and report)