google sheetsgoogle-sheets-query
I recently started to use QUERYs on Google Sheets. I just encountered a problem where after typing my query
=QUERY('Copy of TEST'!A1:E7,"select A, sum(D)
group by A")
it is compounding the A1 row with the Title of my columns (in this case is "Service Type Updates"), and the row A2 that belongs to the first service ("Consultation").
Service Type Updates Consultations sum Tier 1 1
I have tried everything adding an extra row between those to rows but nothing.
No. A pivot table can be created from cells populated by a query.
Check your selection range (etc.).
You can accomplish this in two steps:
Create a new sheet Sheet2 and use the following formula for A1 (or wherever you want the column to begin): =unique(Sheet1!A:A). This will give you a vertical column of simply A, B, C, and D as the unique values in Sheet1's A column.
Combine transpose and filter in the cell adjacent to each cell in Sheet2 as follows: =transpose(filter(Sheet1!B:B,Sheet1!A:A=A1)). Here, you are filtering the second column of your Sheet1 according to whether or not the letter next to in Sheet1 matches the latter next to it in Sheet2. This normally prints vertically, so you use transpose to make it print horizontally, i.e. in the same row as you show above. Then, drag the formula down to apply it to each successive row.
The formulas in Sheet2 end up looking like this:
Best Answer
You can try excluding the header row from the query, by starting the range with
A2instead ofA1: