Creativity Survey - Originality of Text Data

[mwhite17]

Hi,

I'm trying to analyse some data for a creativity survey.

Respondents were asked to generate as many ideas for a film title.

I have around 4000 different responses, and I want to assess the uniqueness of each one by finding out the percentage of respondents that said the same, or a very similar idea. I had used pivot so far, but it only matches cells that are exactly the same.

I have since been using COUNTIF to pick up ideas that contain frequently used words, but doing this for each idea is taking a very long time. I was wondering if someone might know of a faster way of doing this?

Also, I have collated all the respondents ideas into one column of data to easily sort through. Once I have the percentages in an adjacent column, I want to use some sort of formula to lookup the ideas from the raw data tab, to this column, and then for it to transfer the percentage number, back onto the participants tab.

Thanks,
Matt

 

[oldbrewer]

[TABLE="width: 1073"]
<colgroup><col><col><col span="11"><col><col></colgroup><tbody>[TR]
[TD]name[/TD]
[TD]title[/TD]
[TD]bears[/TD]
[TD]Alaska[/TD]
[TD]Alaskan[/TD]
[TD]salmon[/TD]
[TD]run[/TD]
[TD]Amazing[/TD]
[TD]fishing[/TD]
[TD]Natural[/TD]
[TD]summer[/TD]
[TD]hard[/TD]
[TD]days[/TD]
[TD]count of "X"[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name1[/TD]
[TD]bears in Alaska[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name2[/TD]
[TD]Alaskan bears[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name3[/TD]
[TD]the salmon run[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name4[/TD]
[TD]Amazing fishing bears[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name5[/TD]
[TD]Natural Alaska[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name6[/TD]
[TD]Alaskan summer[/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name7[/TD]
[TD]bears in Alaska[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name8[/TD]
[TD]Alaskan bears[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name9[/TD]
[TD]the salmon run[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name10[/TD]
[TD]Amazing fishing bears[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name11[/TD]
[TD]Natural Alaska[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name12[/TD]
[TD]Alaskan summer[/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name13[/TD]
[TD]bears in Alaska[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name14[/TD]
[TD]Alaskan bears[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name15[/TD]
[TD]the salmon run[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name16[/TD]
[TD]Amazing fishing bears[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name17[/TD]
[TD]Natural Alaska[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name18[/TD]
[TD]Alaskan summer[/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name19[/TD]
[TD]A hard days fishing[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name20[/TD]
[TD]Fishing in Alaska[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]counting key words might be a start[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[mwhite17]

This is pretty much what I have been doing so far but it's taking forever.

And also, I won't be summing up the respondents scores, but the number of times bear is mentioned, for example.

Then if Bear has been mentioned by half the sample, then everyone who wrote Bear will get a very low mark for submitting it.

Thanks,
Matt

 

[mwhite17]

Is there a way to search for the most common text strings within cells? For example, say I have cells such as

Bear

Watch out for the Bear

Here come's a Bear


Is there any formula I could use, which finds the most common letter combinations, and then the second most and so on?

Thanks,
Matt

 

[excelltrainee]

What is the criteria for judging creativity? It seems that commonly used works are looked down on. If a user came up with the title, "The Last Frontier". It would score very high on creativity since it has not touched any of your commonly used words.

 

[oldbrewer]

if you total the columns in my post 2 you get 13 Alaska's, 6 Alaskan's and 9 bears

 

[mwhite17]

What is the criteria for judging creativity? It seems that commonly used works are looked down on. If a user came up with the title, "The Last Frontier". It would score very high on creativity since it has not touched any of your commonly used words.

The criteria is just how unique the idea is compared to the rest of the sample. How good the idea is isn't being assessed.

I found this code which works, the only problem is it picks out every single individual word in a column. This is pretty useful, but I'll still have to go through and delete the useless words, such as 'and' 'the' etc.

How to find most frequent used words from text string : excel