Pivot Table for Multiple Text Labels per Row

[V-Man]

Try as I may, I haven't been able to figure this one out - and can't seem to find a similar situation that's been answered before. Here's the skinny:

Using Excel 2007

Background
I have rows containing patient data from follow-up phone calls, including a single text column that contains a summary of concerns the patient had about their visit. An example might be:

Food was terrible. Nurse tried 3 times to start IV.

For each comment, the nurse making the call assigns discrete "labels" for the themes that are mentioned in the comments and records them in an adjacent column called Issues (we can change this approach, if there's a better way to do this). So - for our example above, the labels she assigns in the Issues column might be:

Food Quality, IV Starts

My Question
In the pivot table that summarizes all of this data, I want to count the number of times each label is mentioned in the Issues column. So - something like this:

Food Quality 10
IV Starts 8
Staff Attitude 7
Treatment Delay 5

If the Issues column contains a single label per row, it obviously works perfectly. But how to handle more than one label per row has me stumped. How can I best accomplish this?

TIA for your time and assistance!

V-Man

 

[V-Man]

^bump

Any ideas for this? Thanks!

 

[V-Man]

^last bump

Still need a solution for this - any suggestions?

 

[Charlie45]

You could use =COUNTIF(D:D,"*Food Quality*")

 

[V-Man]

Thanks for your reply!

Yes, however, new themes may be assigned each day - which would require creating a separate COUNTIF column for each new theme. A pivot table works perfectly IF there is no more than one theme per row - but of course, the expressed concerns often contain more than one theme.

Perhaps an example would help. This will be grossly simplified (the actual sheet contains 500+ rows and 60 columns of data, with new rows added daily as calls are made), but here's the idea:

Column A - MRN
Column B - Date of call
Column C - Concerns (free text is entered here)
Column D - Theme 1
Column E - Theme 2
Column F - Theme 3

Example Data:

Row 2
Column C
Food was terrible. Nurse tried 3 times to start IV.
Column D
Food Quality
Column E
IV Starts

Row 3
Column C
Nurse was rude
Column D
Staff Attitude

Row 4
Column C
Had to get Charge Nurse to start IV
Column D
IV Starts

For all of the calls made, I need a way to easily identify (1) all of the themes in columns D and E, and (2) how often each theme has been assigned over all of the calls. So - for the data above, I'd need something like this:

Food Quality - 1
IV Starts - 2
Staff Attitude - 1

Does that help?