CODEWITHSUNDEEP
excel
apogee
apogee

Reputation: 25

Is there a way to determine the frequency of words in a column separated by comma in excel?

I can count the number of times a specific word appears in an excel sheet column using "countifs"

However, is there a way to automatically show the most common words and the frequency of appearance in a column of data? For eg. in the sample image, column A has rows with different words aa, am, aq...separated by a comma. I would like to calculate the most common words and the frequency of appearance as shown by column C.

Sample image

Is there a way to achieve this in Excel?

Upvotes: 1

Views: 307

Answers (1)

Jos Woolley
Jos Woolley

Reputation: 9062

For O365:

=LET(α,A1:A10,β,FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(", ",,α),", ","</b><b>")&"</b></a>","//b"),γ,TRANSPOSE(UNIQUE(β)),δ,MMULT(SEQUENCE(,ROWS(β))^0,N(β=γ)),TRANSPOSE(SORTBY(CHOOSE(SEQUENCE(2),γ,δ),δ,-1)))

Upvotes: 2

Related Questions