google sheetspivot table
I want to show the "values as rows" on the left side of my pivot table on "rows" on the right.
In other words, I want my pivot table rows to be grouped by value rows instead of regular rows.
Current situation:
So, essentially, what I'd like to achieve here is to switch the places for "Channel" and "Values" to get something like this:
Is there any way to build this using a pivot table?
The FILTER command does this nicely. Suppose this is your table.
A B C
1 Beer Brewery Rating
2 abc QQ 1
3 qwe WW 3
4 wer EE 5
5 ert WW 2
6 dfg QQ 4
7 sdf WW 3
Then add
9 Brewery MaxRating Favorites
10 WW
where
B10 = MAX(FILTER(C2:C7, B2:B7=A10)
which find the best rating for the brewery listed in A10; and
C10 = JOIN(", ", FILTER(A2:A7, B2:B7=A10, C2:C7=B10))
which finds all beers from that brewery with the maximal rating. Result:
9 Brewery MaxRating Favorites
10 WW 3 qwe, sdf
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