Finding the top phrases containing 1, 2, 3 or 4 words in a list and how many occurrences

[lunap04]

Hi, I have a list of around 40K rows with keywords in a column and I'm working on a way to go through this list and see which keyword cluster would be relevant to have.

I would like to get a list of:

- which same word is repeated across this list and how many times
- which 2 words (close to each other) are repeated and how many occurrences
- which 3 words (close to each other) are repeated and how many occurrences
- which 4 words (close to each other) are repeated and how many occurrences

In a nutshell, it should be something similar to what this tool does Text Analyzer - Text analysis Tool - Counts Frequencies of Words, Characters, Sentences and Syllables but in excel.

Thanks a lot in advance to anyone who will offer a solution.

 

[padapinto]

Hi, I have a list of around 40K rows with keywords in a column and I'm working on a way to go through this list and see which keyword cluster would be relevant to have.

I would like to get a list of:

- which same word is repeated across this list and how many times
- which 2 words (close to each other) are repeated and how many occurrences
- which 3 words (close to each other) are repeated and how many occurrences
- which 4 words (close to each other) are repeated and how many occurrences

In a nutshell, it should be something similar to what this tool does Text Analyzer - Text analysis Tool - Counts Frequencies of Words, Characters, Sentences and Syllables but in excel.

Thanks a lot in advance to anyone who will offer a solution.

a snap of the data you are working with will be nice to show.

 

[lunap04]

a snap of the data you are working with will be nice to show.

Hi @padapinto, I'm trying to categorise/cluster a large number of words and phrases in a column but I don't know which cluster or category to use for each of these words/phrases.

I was trying to find a way to go through this list and create groups or clusters based on how many times a single word (or ideally 2, 3, and 4 words) appears throughout the list. Ideally, it would be good to see which 1, 2, 3, and 4 words appear the most throughout this list (so to find out which clusters to focus on) and then get a list of words that are in that cluster.

Attached a screenshot of the data I'm working to give you an idea.

 

[sandy666]

I could say use Power Query but you are on MacOS
anyway use XL2BB to post your example , not a picture

 

[padapinto]

Hi @padapinto, I'm trying to categorise/cluster a large number of words and phrases in a column but I don't know which cluster or category to use for each of these words/phrases.

I was trying to find a way to go through this list and create groups or clusters based on how many times a single word (or ideally 2, 3, and 4 words) appears throughout the list. Ideally, it would be good to see which 1, 2, 3, and 4 words appear the most throughout this list (so to find out which clusters to focus on) and then get a list of words that are in that cluster.

Attached a screenshot of the data I'm working to give you an idea.

I HAVE USED FORMULAS to get the results of combined 2, and 3 words but first you need to break the phrase using column to text.
you can check extended office website for text to column then after that you apply my formulas. to highlight the most phrases with 2 words, i have used a formula in conditional formats to get that =$J119=$J$111:$J$118. when writing conditional format, you just click the cells that are involved thus my formula is relating to row 119 because the table is in that area. you can see the snapshot

query	BROKEN WORDS	1 WORD	2 WORDS	3 WORDS	4 WORDS	2 WORDS	3 WORDS	4 WORDS	2 WORDS	3 WORDS	4 WORDS
zoom background tv sets	zoom	backgroundS	TV	sets	zoom	zoom backgroundS	zoom backgroundS TV	zoom backgroundS TVsets	4	2	1
zoom background quiz show	zoom	backgroundS	quiz	show	zoom	zoom backgroundS	zoom backgroundS quiz	zoom backgroundS quizshow	4	1	1
zoom bacgrounds game show	zoom	backgroundS	game	show	zoom	zoom backgroundS	zoom backgroundS game	zoom backgroundS gameshow	4	1	1
zoom background funny	zoom	backgroundS	TV		zoom	zoom backgroundS	zoom backgroundS TV	zoom backgroundS TV	4	2	1
youtube channel virgin	youtube	channel	virgin		youtube	youtube channel	youtube channel virgin	youtube channel virgin	1	1	1
youtube on virgin tv	youtube	on	virgin	tv	youtube	youtube on	youtube on virgin	youtube on virgintv	1	1	1
your account	your	account			your	your account	your account	your account	1	1	1
young ones	young	ones			young	young ones	young ones	young ones	1	1	1
								TOTALS	4	2	1

1 WORD	2 WORDS	3 WORDS	4 WORDS	2 WORDS	3 WORDS	4 WORDS
=B111	=B111&" "&C111	=G111&" "&" "&D111	=CONCATENATE(H111,E111)	=COUNTIF($G$111:$G$118,G111)	=COUNTIF($H$111:$H$118,H111)	=COUNTIF($I$111:$I$118,I111)
=B112	=B112&" "&C112	=G112&" "&" "&D112	=CONCATENATE(H112,E112)	=COUNTIF($G$111:$G$118,G112)	=COUNTIF($H$111:$H$118,H112)	=COUNTIF($I$111:$I$118,I112)
=B113	=B113&" "&C113	=G113&" "&" "&D113	=CONCATENATE(H113,E113)	=COUNTIF($G$111:$G$118,G113)	=COUNTIF($H$111:$H$118,H113)	=COUNTIF($I$111:$I$118,I113)
=B114	=B114&" "&C114	=G114&" "&" "&D114	=CONCATENATE(H114,E114)	=COUNTIF($G$111:$G$118,G114)	=COUNTIF($H$111:$H$118,H114)	=COUNTIF($I$111:$I$118,I114)
=B115	=B115&" "&C115	=G115&" "&" "&D115	=CONCATENATE(H115,E115)	=COUNTIF($G$111:$G$118,G115)	=COUNTIF($H$111:$H$118,H115)	=COUNTIF($I$111:$I$118,I115)
=B116	=B116&" "&C116	=G116&" "&" "&D116	=CONCATENATE(H116,E116)	=COUNTIF($G$111:$G$118,G116)	=COUNTIF($H$111:$H$118,H116)	=COUNTIF($I$111:$I$118,I116)
=B117	=B117&" "&C117	=G117&" "&" "&D117	=CONCATENATE(H117,E117)	=COUNTIF($G$111:$G$118,G117)	=COUNTIF($H$111:$H$118,H117)	=COUNTIF($I$111:$I$118,I117)
=B118	=B118&" "&C118	=G118&" "&" "&D118	=CONCATENATE(H118,E118)	=COUNTIF($G$111:$G$118,G118)	=COUNTIF($H$111:$H$118,H118)	=COUNTIF($I$111:$I$118,I118)
				=MAX(J111:J118)	=MAX(K111:K118)	=MAX(L111:L118)

 

[Akuini]

I wrote a macro to generate phrase frequency.
Maybe this is what you want:
count-word-and-random-phrase-frequency

Please read post #13 & #19 in that thread