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

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

protected by user0 Aug 30 at 19:42

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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