Trend Analysis of Survey Results

**Thomo88** · 10-23-2013, 09:49 AM

Wondering if anyone knows direct answer to my question or has previously come across a similar request and has an alternative work around.

I have an excel sheet that contains around 3,500+ rows of data, that is the results / answers from a survey that was sent out (each row being an individuals answers). One of the fields in the survey asks for comments, and these are recorded in Column D ... comments can be 2 words or multiple sentances in length.

I have been asked to analyse the comments to build a trend on the most popular themes that are discussed.

Firstly:
Any ideas on how to best acheive this?

My thoughts / actual question: .... is excel able to identify individual words within a string / cell then take all the words and populate into a list. So in theory, if this was possible, I would then have an extensive list that contained every word used in the comments for all responses, thus enabling me to use countifs to identify most "common" words.

Any and every thought welcome

**Jakobshavn** · 10-23-2013, 09:52 AM

It would be easy, using macros, to construct a list of all the words appearing in column D and the number of times they appear. Is a macro approach acceptable to you??

**Andy Pope** · 10-23-2013, 10:17 AM

You could start by using a pivot table.
Depending on the actual text you may need to split out the verbatim comments, using text to columns. You can then build a column of respid and words with which to use pivot tables on.

One thing to consider though when analysing words is the potentional lost of context in the sentence.

**Thomo88** · 10-23-2013, 10:22 AM

Thanks for responses so far!

Jakobshavn: Would be favourable option of mine to use a macro ... but have no experiance on scripting such a query

**Jakobshavn** · 10-23-2013, 11:35 AM

I have attached a workbook to demonstrate the technique. The macro moves down column D and builds up a collection of all words and a collection of unique words. The unique words are placed in column E and the full list in column G. Column F is then filled with a count of how many times each unique word appears in the full list.