Search code examples
google-sheets

How do I retrieve the most frequent text value in a column that has cells with multiple entries in Google sheets?

I have a column with data (movie genres in this case) that looks like this:

| Drama                     |
| Action, Drama             |
| Action, Adventure, Drama  |

I am trying to get the most frequent occurring value, which is 'Drama' in this case. I came up with the following formula as a beginning but even that doesn't do what I want it to do.

ARRAYFORMULA(COUNT(UNIQUE(TRIM(SPLIT(A1:A3, ",")))))

This returns 0 but I want it to return 3 in this case and then find the most frequent unique value. Any ideas?

Solution

  • Basic case

    =QUERY(index(if({1,1},TRANSPOSE(SPLIT(JOIN(", ",FILTER(A:A,A:A<>"")), ", ")))), "select Col1, count(Col2) group by Col1 order by count(Col2) desc")

    enter image description here

    Notes:

    • transpose + split + join is to get the line with all values
    • index + if({1,1}... is to double the column, for query to work. See this trick on sample file.
    • query is to select the most frequent values on the top.

    Get only top value

    use index:

    =index (original_formula, 2, 1)