Google-sheets – Script that reads rows and columns in a sheet and returns strings with frequency of occurrence

google sheetsgoogle-apps-script

first time caller!

I have a single column that contains comma separated names (lots of names) — I am trying to find a script that can read the comma separated string entries in cell and give me a frequency count of each unique word. For example,

Dave
Jack
Anita, Peter
Peter
Kate, Noor
Noor
Peter
Peter, Dave

The expected output will be

Anita – 1
Dave – 2
Jack – 1
Kate – 1
Noor – 2
Peter – 4

Looking for any help. I know nothing about apps script and I am a novice sheets user too.

Best Answer

You have a cell containing a number of words. Some words are separated by spaces and some words are separated by spaces AND commas; there appears to be no pattern for the occurrence of spaces and commas.

You want to count the occurrences of the unique words, then display the result in a single cell. The format of the cell is the word, dash, count, space; and repeat.

There are likely to be many possible solutions to your question. Please consider this answer as one possible solution.

Assume your data is in Cell B1, enter this formula in Cell B2:

=join(" ",ARRAYFORMULA(query({query({transpose(split(substitute(B1,",","")," ",true,true))},"select Col1, count(Col1) where Col1 != '' group by Col1 order by Col1 asc label count(Col1) ''",),query({transpose(split(substitute(B1,",","")," ",true,true))},"select Col1, count(Col1) where Col1 != '' group by Col1 order by Col1 asc label count(Col1) ''",)}, "select Col1 where Col1 = Col3")&"-"&query({query({transpose(split(substitute(B1,",","")," ",true,true))},"select Col1, count(Col1) where Col1 != '' group by Col1 order by Col1 asc label count(Col1) ''",),query({transpose(split(substitute(B1,",","")," ",true,true))},"select Col1, count(Col1) where Col1 != '' group by Col1 order by Col1 asc label count(Col1) ''",)}, "select Col2 where Col1 = Col3")))

Screenshot

Explanation:

  • substitute(B1,",",""): removes any commas
  • split(substitute(B1,",","")," ",true,true): separate the words (delimiter=one space).
  • transpose(split(substitute(B1,",","")," ",true,true)): List in a column
  • query({transpose(split(substitute(B1,",","")," ",true,true))},"select Col1, count(Col1) where Col1 != '' group by Col1 order by Col1 asc label count(Col1) ''",): create a list of unique names and the count of occurances, sorted in alphabetical order.
  • query({<<query>>=Col1,<<query>>=Col1 and Count(Col1), "select Col1"}&"-"&query({<<query>>=Col1,<<query>>=Col1 and Count(Col1),"select count(Col1)"}): combine the Name (Col1), a dash, and the Count (count(Col1)). Note: output = only one row
  • Arrayformula(<<query>>): performs the preceeding query for all rows
  • join(" ",Arrayformula(<<query>>)): converts the column to a single cell; delimited = one space.
Related Topic