0

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.

1

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")

| improve this answer | |
0
  • you can do it all in one go like this:

    =ARRAYFORMULA(QUERY(TRANSPOSE(SPLIT(CONCATENATE(
     IF(Sheet1!D:G<>"", "♦"&Sheet1!D:G, )), "♦")), 
     "select Col1,count(Col1) 
      group by Col1 
      order by count(Col1) desc 
      limit 5 
      label count(Col1)''", 0))

    0

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.