PLEASE HELP!! How to find top 10 most common terms in order of occurance

[mg93108]

Please help me!

The concise question is: Given a large number of rows (7500) how do I find the words that are most commonly used in a given column? I need to identify the top 10 in order

Extra info: I’m enrolled in a marketing class and apparently there is an assumption that you know how to use pivot tables and a bunch of stuff that I never learned. The class itself is not about spreadsheets yet we need to use them to analyze the data. I’ve watched videos and read online but can’t seem to figure this out.

Thanks so very much!!!

[robleyd]

Help for Pivot tables with a link to download a sample file.

If there are multiple words in a single cell, you may need more than a pivot table to identify counts of separate words.

If this answer helped you, please accept it by clicking the check mark to the left and, karma permitting, upvote it. That will help other people with the same question.

In case you need clarification, edit your question (not an answer) or comment the relevant answer.

[mg93108]

What would you advise? Do you know how to solve this?

[robleyd]

I can’t advise as I am not sure what the exact problem is. If the words are one to a cell, use pivot tables. If not, please elaborate. A sample spreadsheet might also be useful.

[keme]

If a subject requirement for your class is that you know how to use spreadsheets, you need to learn this. Start studying. With the info you provided in the question, the answer from Robleyd is the best advice you can get. Spend some time with the sample file!

For this particular problem: If you are more specific about source data, you will get more specific advice. We will not do your school work for you, but we will try to help you to help yourself.

[mg93108]

Keme,
You haven’t offered any help in answering the question. You asked for specifics. My original and concise question was "Given a large number of rows (7500) how do I find the words that are most commonly used in a given column? ". I’m not asking anyone to “do my homework” for me. I’m asking “how” one goes about utilizing Calc to solve this. Your only advice is to “start studying”. I’ve spent a great deal of time reading and watching videos related to this. The information on “how” to analyze this data is not available to me through class instruction. The class is not about using a spreadsheet program. It’s about what the data tells us and how we use that to inform decisions in the area of marketing. I found someone with an MS in Data Analysis. She said given more than one word in a cell, she can’t see how Calc can do it. Maybe she and I are missing something. I’m looking for an “answer”, not a commentary on study methods. I hoped this forum would help.

[keme]

Well, I pointed another finger at what robleyd mentioned: To give a more specific answer we need a more specific question. For starters, answer the question robleyd posed (by way of a conditional, and more than once). Do you have a single word or multiple words in each cell? Albeit you quoted the MS as saying " … given more than one word …" you didn’t explicitly state that such is the case.

A few others that may be relevant:

• If a cell can contain multiple words, how are they separated? (Spaces and punctuation like in sentences? Comma separated? May a word repeat within a cell?)
• Do you have several columns from which to build a rank?
• If so, do you need a mechanism for picking a particular column, or do you want “all at once”?

We are here to help. We generally offer sound advice to our best ability. The advice may come out tersely, for a number of reasons. That is not always intended. I regret that mine was taken that way in this case.

[keme]

Also, having a master degree in data analysis does not necessarily imply expert knowledge of spreadsheets. There are other tools which offer more “analytical power”, so commonly a spreadsheet is only used for the basics and on limited size data chunks, perhaps to clean up your dataset and perform some sensibility checks.

That said, it is correct that a spreadsheet is probably not an optimal tool for this kind of list processing.