CODEWITHSUNDEEP
google-sheets
Moseleyi
Moseleyi

Reputation: 2879

Google Sheets - QUERY - Condense Pivot table

I have a detailed data like this:

Week        | Type      | Number
--------------------------------
21 Oct 2020 | black     | 3
--------------------------------
21 Oct 2020 | black     | 4
--------------------------------
21 Oct 2020 | red       | 2
--------------------------------
28 Oct 2020 | black     | 5
--------------------------------
28 Oct 2020 | green     | 7

From this I use a QUERY function to create this table:

Week        | black | red | green
--------------------------------
21 Oct 2020 | 7     | 2   |
--------------------------------
28 Oct 2020 | 5     |     | 7

The thing is that the week is not as important as the sequence of the number. Ideally I'm after something like this

     | black | red | green
--------------------------
     | 7     | 2   | 7
--------------------------
     | 5     |     |

Though I don't mind if there were a simple counter in the first column

     | black | red | green
--------------------------
1    | 7     | 2   | 7
--------------------------
2    | 5     |     |

Essentially, it's grouped by whether it's first week, second, et cetera, but I can't figure out how to do it. I don't mind a second QUERY but I would like to then plot the numbers in a line chart but if I use the dates, I will have gaps. Perhaps somebody knows a trick I don't..

Upvotes: 0

Views: 68

Answers (1)

ADW
ADW

Reputation: 4247

Here is one way to tackle this.

And it gets you to this solution but it needs a helper column:

     | black | red | green
--------------------------
     | 7     | 2   | 7
--------------------------
     | 5     |     |

Condense solution query

Link to Google Sheet.

You will need a helper column to create a counter. This counter acts as the row number in the output.

The formula in the helper column is:

=match(A3, unique(filter(A:A, B:B = B3)))

Explanation:

  • filter dates in colA that match the colour in colB
  • unique removes duplicate dates
  • match creates the counter

Then use this data table for the query.

F3: =query(B3:D8,"select C, sum(D) group by C pivot B")

Upvotes: 2

Related Questions