Pivot Table for Multiple Text Labels per Row

V-Man

New Member
Joined
Mar 4, 2011
Messages
4
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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?
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top