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]

name

title

bears

Alaska

Alaskan

salmon

run

Amazing

fishing

Natural

summer

hard

days

count of "X"

name1

bears in Alaska

X

X

2

name2

Alaskan bears

X

X

X

3

name3

the salmon run

X

X

2

name4

Amazing fishing bears

X

X

X

3

name5

Natural Alaska

X

X

2

name6

Alaskan summer

X

X

X

3

name7

bears in Alaska

X

X

2

name8

Alaskan bears

X

X

X

3

name9

the salmon run

X

X

2

name10

Amazing fishing bears

X

X

X

3

name11

Natural Alaska

X

X

2

name12

Alaskan summer

X

X

X

3

name13

bears in Alaska

X

X

2

name14

Alaskan bears

X

X

X

3

name15

the salmon run

X

X

2

name16

Amazing fishing bears

X

X

X

3

name17

Natural Alaska

X

X

2

name18

Alaskan summer

X

X

X

3

name19

A hard days fishing

X

X

X

3

name20

Fishing in Alaska

X

X

2

counting key words might be a start

<colgroup><col><col><col span="11"><col><col></colgroup><tbody>
</tbody>

 

[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