formula to identify most commonly occurring words

[slinky192001]

Hello

In column A there are text strings:

printer has an error working
backup required
user reported error with printer
password reset requested
Printer toner required
User needs her password reset
Printer is jammed
User requires access to a printer

I need a formula that will return the top 5 or 10 or 20 most commonolly occuring words in this column A. So in the above example it would be Printer. However if possible i would want the top 10 or 20 as a expect from the list i need to use the most commonolly used might be "User" or "the" which wont give me what i need.



Is this possible?

 

[number kruncher]

For a non-VBA list, try something like..

In Column C, list all the words which appear in Column A, eg

Printer
User
etc

In Column B, use COUNT to count each occurence, eg.

=COUNTIF(A:A,"*"&C1&"*")

Then use VLOOKUP & LARGE to interogate this list, to whatever size of list you need, eg.

VLOOKUP(LARGE(B:B,1),B:C,2,FALSE)

 

[slinky192001]

Ok thank you. My list is 21566 rows....what is the VB option? i am not very familar with this but a friend i work with is.

Thank You

 

[number kruncher]

Slinky,

My VBA is not upto this task, however, I feel with the number of variables you are listing, the result would be be quite complex.

Edit: To my earlier post, where two or more words appear with the same number of appearances, you may need to setup an extra test to avoid a word from being omitted in your top 5/10/20 list.

Thanks for your positive response, there are no medals for helping, however, a thank you is most appreciated.