0

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.

0

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.
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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