Thursday, February 7, 2019

yogi_Grouping pivoting and aggregating multiple columns with text

Google Spreadsheet   Post  #2588

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Feb-07-2019
question by: Reuben Robbins:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/1s_C0yEOFd4;context-place=forum/docs
Grouping, pivoting and aggregating columns multiple columns with text
I am trying to rearrange data from a big table with QUERY and IMPORTRANGE. Some of the cells contain text. The formula is:
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/xxxxxxxxxxxxx", "Answer_Data!T2:BM"), "Select Col1, sum(Col8) where Col4 = 'langhandcg' and not Col6 contains 'Measure_' and not Col6 contains 'Item_' Group by Col1 pivot Col5 label Col1 'pid' ",0)

Here is a sample sheet that lays out my issues:


The issue is that I'm not truly summing col8, but in order to get the data to be filtered and aggregated the way I want, I can only think of doing a pivot table.

Is there another way I can get my data arranged how I want with QUERY and IMPORTRANGE? Or with other formulas?

--------------------------
Note:
In developing my solution I did not have to pivot the data ... if the data does have to be pivoted then my resulting can be transposed


1 comment:

  1. Thankyou this is a very helpful really appreaciated
    Very good, I think I found the knowledge. I will see and refer some information in your post. thank you.
    DAEMON Tools Pro Crack
    Bandicut Crack
    TubeMate Downloader Crack
    VNC Connect Enterprise Crack
    Tally ERP 9 Crack

    ReplyDelete