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.
Is there a way to achieve this in Excel?
CodePudding user response：
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)))