common keyword identification

[JamesArmitage]

Looking to identify and count the most common and top 5 words based on a criteria (pupil name) from a range of cells which have copy and pasted sentances in them. Essentially, i am looking to highlight a word content / writing style over a number of examples and then order them by count. I would like to do this on two critera, pupil name and book title but don't want to get ahead of myself in aspiration.

e.g.
Column A has the name of a pupils
Column A will have the same pupil multiple times and there are 30 pupils and order of pupil's sentance/paragraph entry is random with maybe 350 entries in total
Colomn B has book name
Column C has a entance/paragraph written by the pupils

Not sure if Excel can do this but any thoughts or suggestions welcome. Thanks

[63falcondude]

Attach a sample workbook.

Make sure there is just enough data to demonstrate your need.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[JamesArmitage]

Attached an example.

Essentially looking to apply a formula filter based on two parameters to look up keywords from a list provided from a column's cell content.

I may be asking for something a little complicated and something that makes excel slightly unstable? Any help or recommendations greatly appreciated. Thank you in advance.

[63falcondude]

Here's one way based on the layout of your sample in post #3:

J6 =COUNTIF(F:F,"*"&I6&"*")

Drag this formula down through J221.

Most Common Keyword =INDEX(Keywords,MATCH(LARGE(J6:J221,1),J6:J221,0))
Second Most Common =INDEX(Keywords,MATCH(LARGE(J6:J221,2),J6:J221,0))
Third Most Common =INDEX(Keywords,MATCH(LARGE(J6:J221,3),J6:J221,0))