In Google Sheets, how can one aggregate values (MAX, MIN, AVERAGE) based on the result of splitting a string in a different column?

Question

If I had a table with the following values, how can I generate the following table? The result table separates out each tag and calculates the number of rows with the tag mentioned (COUNT) and also the max value for a given tag.

    +-----+-------------+
    | VAL | TAGS        |
    +-----+-------------+
    |  4  | html, css   | 
    +-----+-------------+
    |  2  | js, ts      |
    +-----+-------------+
    |  3  | js, css     |
    +-----+-------------+

    +------+-------+-------+
    | TAG  | COUNT |  MAX  |
    +------+-------+-------+
    | html |   1   |   4   |
    +------+-------+-------+
    | css  |   2   |   4   |
    +------+-------+-------+
    | js   |   2   |   3   |
    +------+-------+-------+
    | ts   |   1   |   2   |
    +------+-------+-------+

score 1 · Accepted Answer · answered Dec 26 '19 at 02:50

1

try:

=ARRAYFORMULA(QUERY(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF(IFERROR(SPLIT(B3:B, ","))<>"", "♀"&A3:A&"♦"&SPLIT(
 SUBSTITUTE(B3:B, ", ", ","), ",")&"♦", )),,999^99)),,999^99), "♀")), "♦"), 
 "select Col2,count(Col1),avg(Col1),max(Col1),min(Col1) 
  group by Col2
  label Col2'tags'"))

answered Dec 26 '19 at 02:50

player0

124,011
12
67
124

Wow, that is a gnarly formula! But it looks like it works. I actually ended up implementing a solution using custom formulas. Do you think your solution would be faster or slower? – JustKash Dec 26 '19 at 03:13
depends... on such a small sample it doesn't matter - speed wise it would be the same. – player0 Dec 26 '19 at 11:26
That was just a sample I created for the question, the real dataset has hundreds of rows. – JustKash Dec 27 '19 at 02:31

user11982798 · Answer 2 · 2019-12-26T03:12:59.083

Use formula like this:

=query (
   arrayformula (
      split (
         transpose (
            split (
               textjoin (
                     ",",true, 
                     arrayformula (
                         {filter(A90:A,A90:A<>"") & "✫" & 
                          trim(split(filter(B90:B,A90:A<>""),","))
                         }
                     )
               ),",")
            ),"✫"
         )
      ), 
   "select Col2, count(Col2), max(Col1) where Col2 is not null group by Col2  label Col2 'Tag'")

In Google Sheets, how can one aggregate values (MAX, MIN, AVERAGE) based on the result of splitting a string in a different column?

2 Answers2