How to list the most frequent 3-word strings on Google Sheets

Question

I have a list of industries with a adjacent list of industries to categorize them. I would like to know which industries are the most common but I don't manage to make Sheets interpret two-word categories as one.

First, I would like to know which 5 categories are the most common overall. Also I would like to know the top 5 one-word (black), two-word (red) and three-word (blue) categories.

Plus, I would like to get rid of the commas.

Here's what I want to achieve and a link to a google sheets document where I've laid out all the data:

https://docs.google.com/spreadsheets/d/13N8gc4POPhFhTvyqq-UugWS5GCgcONwliacSL8-MAr8/edit#gid=0

How can these categories be grouped and listed?

can you make the sheet editable so it's a little eaiser to demo/test solutions? — MattKing, Apr 06 '20 at 19:24
@MattKing you probably already know, but to let everyone know, you can press `File->Make a copy` on any spreadsheet to be able to edit an otherwise locked sheet. — CodeCamper, Apr 06 '20 at 21:11

score 2 · Accepted Answer · answered Apr 06 '20 at 23:09

2

overal word:

=ARRAYFORMULA(QUERY(TRANSPOSE(SPLIT(QUERY(B3:B11&",",,99^99), ", ")), 
 "select Col1,count(Col1) 
  group by Col1
  order by count(Col1) desc
  limit 5
  label count(Col1)''"))

overall phrase:

=ARRAYFORMULA(QUERY(TRANSPOSE(SPLIT(QUERY(B3:B11&",",,99^99), ",")), 
 "select Col1,count(Col1) 
  group by Col1
  order by count(Col1) desc
  limit 5
  label count(Col1)''"))

one word:

=ARRAYFORMULA(QUERY(TRIM(TRANSPOSE(SPLIT(QUERY(B3:B11&",",,99^99), ","))), 
 "select Col1,count(Col1)
  where not Col1 contains ' '
  group by Col1
  order by count(Col1) desc
  limit 5
  label count(Col1)''"))

two words:

=ARRAYFORMULA(QUERY(TRIM(TRANSPOSE(SPLIT(QUERY(B3:B11&",",,99^99), ","))), 
 "select Col1,count(Col1)
  where Col1 matches '\w+ \w+'
  group by Col1
  order by count(Col1) desc
  limit 5
  label count(Col1)''"))

three words:

=ARRAYFORMULA(QUERY(TRIM(TRANSPOSE(SPLIT(QUERY(B3:B11&",",,99^99), ","))), 
 "select Col1,count(Col1)
  where Col1 matches '\w+ \w+ \w+'
  group by Col1
  order by count(Col1) desc
  limit 5
  label count(Col1)''"))

answered Apr 06 '20 at 23:09

player0

124,011
12
67
124

matches seems to be a dangerous Query string, I'm surprised it even works here, it seems when you use the | in Query matches it only runs locally and not on the google server. – CodeCamper Apr 07 '20 at 00:54
It seems to work wonders. Is there any way I could use this formula to do this exact same operation but with actual sentences? In that case, I would like to omit obvious words that are used a lot in language like articles, pronouns, most common verbs, etc. Ideally, I would like to have a combination of adverbs, adjectives and nouns. If this is possible, any chance you could point me to the right direction? – djur Apr 07 '20 at 06:47
@djur You should ask a separate question that is more specific with an sample of behavior you desire. – CodeCamper Apr 07 '20 at 13:24
@CodeCamper, I just did that: https://stackoverflow.com/questions/61075050/identifying-adverb-adjective-noun-strings-in-a-list-of-sentences-on-google-sheet – djur Apr 07 '20 at 14:07

CodeCamper · Answer 2 · 2020-04-07T15:39:51.687

1

Breaking the problem into 3 formulas will allow you to support as many "words" as you want.

Step 1) put formula in D29 treat all the words as a single word (looking at your question it seems like this is the only step you really need)

=query(arrayformula(trim(substitute(transpose(split(query({substitute(B3:B," ","_")},"select * where Col1 is not null",counta(B3:B)),", ")),"_"," "))),"select Col1, count(Col1) group by Col1 order by count(Col1) desc label Col1 'Descriptions', count(Col1) 'Frequency'")

Step 2) put formula in F29 Place this next formula next to the table produced by the formula above. D30:D should be replaced if you use different ranges.

=arrayformula({"Words";if(D30:D="","",1+LEN(D30:D)-len(SUBSTITUTE(D30:D," ","")))})

Step 3) put formula in G29 This will output the largest frequency ordered by word count D29:F should be replaced if you use different locations

=query({D29:F},"select * where Col1 is not null order by Col3,Col2 desc")

The advantage to doing it this way is you support 1,2,3,4... word frequency.

edited Apr 07 '20 at 15:39

answered Apr 06 '20 at 21:30

CodeCamper

6,609
6
44
94

Whereas Step 1 does one of the things I want to achieve, I am unsure as to what the other two steps are really doing. Could you please explain further? I've compiled your answer in the page "Copy II" in the same document. – djur Apr 07 '20 at 07:59
@djur try putting step 1 in cell `D29` see the edit I included the cell to put the formula – CodeCamper Apr 07 '20 at 13:47
I've just realized how your code works. Quick question: how can I modify the code to exclude certain values such as a preposition or a certain verb? – djur Apr 07 '20 at 14:28

How to list the most frequent 3-word strings on Google Sheets

2 Answers2

Linked