count most common words

[pep1379]

Hi

I need to rank most common words from a questionnaire with open ended answers .
example there are 6000 rows with open ended answers. i just need to report on most occurring words.

can anyone help?

many thanks

Asparagus
yams, mashed potatoes, cauli,
Cauliflower
Cauliflower.
beans
mashed and squash
red cabbage and chestnuts
Asparagus
capsicum, beans, peas, sweet potato, pumpkin
squash, corn
green beans
green beans
Pumpkin sweet potato peas
Boiled potatoes
Yams
Sweet potato, beans
Boiled potatoes
Potato salad, green leaves salad
Mashed potatoes
sweet potatoes
mashed potatoes, squash and asparagus
Peas corn
zucchini, asparagus
Sweet potato
sweet potatoes cooked with onions and apple
Lots of sides
Sweet potato
Onions
soup
Corn

[XOR LX]

Hi.

So sometimes there'll be just one entry in a cell and sometimes multiple entries can appear in a single cell?

If multiple, what are the possible separators? Looking at your list, I would guess that it's either a comma or else the word "and". Are there any more?

How many do you wish to return? Top 10 most common?

I think posting an actual workbook with an example plus your expected results would be very helpful.

Regards

[Sam Capricci]

looking at your list I believe you will need to do a lot of hand work with it. I'd copy it to another sheet then do a text to columns then take each column and use remove duplicates and combine columns until you end up with one column without duplicates. Then if you want cauli to be considered the same as cauliflower then you'd need to do clean up. Then use countif for the different columns you broke it into.

[pep1379]

Hi there are multiple separators. I cant attach the file as it is too large. I would like the top 30 if possible

[XOR LX]

Without knowing precisely what those separators are it will be almost impossible to help you.

Re attaching a file, it doesn't have to be your actual file. I'm sure you would be more than willing to create a mocked-up, reduced version with dummy data to help support your query.

Regards

[CAABYYC]

I guess you are checking which item in the menu the customer liked to order. if yes, my suggestion is that provide another list of the item in the menu, that will be more easier to give out the answer.