concatenateformulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-query
I have a Google sheet with text strings listed down columns D through G. I want to, on another sheet, display a list of the top, say, 5 most frequent unique values from the first sheet in descending order and also display the number of times each one shows up.
How can I do this?
Tried finding something online already to do this, but stumped how to have the frequency count show up and how to handle the values appearing across a range of multiple columns.
As of 11-12-2013 the COUNTIFS function is available in the new Google Sheets:
See following reference, on how to activate the new Google Sheets:
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
I found a way to do this.
First, I brought all the unique values from Columns D-G of Sheet1 to a single column in Sheet 2 with this:
=SORT(UNIQUE({'Sheetname'!D2:D;'Sheetname'!E2:E;'Sheetname'!F2:F;'Sheetname'!G2:G}))So that sorted the names into alphabetical order (Col B) and number of instances of each in the next column (Col C). Then, on that Sheet2, in another column, I used this to pull the top 5.
=query(B2:C,"Select B, C where B<>'' Order by C Desc limit 5")