Summarizing textual data (using Pivot Tables?)

[xl2008]

Hello all,

I have done several internet searches on this topic, but not quite hit on the right combination of answers yet.

I am using Office 2003.

I have four columns of data.

Column A is salesperson name (textual)
Columns B and C are numerical (a 1-10 rating)
Column D is comments on each salesperson (textual)

I want to create an analysis that will allow me to deep dive into the comments made, and sort them by category of response. Category is determined by the combination of numerical ratings in Columns B and C.

I have already summarized these responses into six main groups, depending on the Column B and C numerical data. I have a summary off to the side that shows me the number, and percentage, of responses that fall into each group.

There is a total of 600 lines of data.

So, ideally, I want to create a pivot table that will show me all the comments from people who scored a 8 or above on Column B *and* C...or less than a 5 on Column B, but a 10 on Column C . . .or any other permutation (hence the 6 categories I've already created).

Still with me?

What I'm thinking of doing now is using a SUM(LEN array to total up the incidence of certain words in the comments section...and then display those counts in a pivot table.

But this would be a simple count, and I would really prefer to be able to sort and see, visually, all the comments associated with certain groups of scores.

I know Pivot Tables don't allow you to display text in the data area. Maybe there is another tool for this?

Thanks in advance for any insight.

 

[whiskycan]

Hi,

You are right about not being able to include text in pivot table data and having a pivot table that feeds off another pivot table seems a bit messy, however you could try this...

Beside your original table of data add column for each word you want to identify using a formula something like this:

=IF(ISERROR(FIND("YourWord",A1)),0,1)

This data could then be made into a pivot table and you can then drill down on the data to see the comments behind it.

Hope that helps

W

 

[xl2008]

Thanks for the reply.

My original data is not in a Pivot Table (didn't mention that).

Counting up individual words on a line-by-line basis strikes me as a brute-force method that also introduces my bias (since I would pick what words to look for).

What I really want is just to be able to say to Excel, "Show me everyone who fell into this category as per their rating on these two factors, and then show me all the comments they made"

. . . and then maybe do a frequency count of some sort to pick out the most frequently occurring words / phrases.

But it doesn't seem like Excel can do that. The FREQUENCY function is for numerical data only, and so too are pivot tables. I saw a frequency program that would do a word count in Excel, but it was asking $19.95. How disappointing.

 

[Yogi Anand]

Hi xl2008:

Welcome to MrExcel Board!

I suggest you post some sample data along with your expected result(s), so we can see what you are working with and what you are trying to accomplish.

 

[whiskycan]

I see what you mean, it's a tough one and it's getting too late for me. There might be an answer to it within the "Text to Columns" function, however the comments would always need to be less than 250 words (unless you are using excel 2007). I'll let you know if I figure anything out, good luck.