MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Pivot Tables & text information

Posted by Diane on April 18, 2001 11:37 AM

I am using a Pivot Table to analyze Human Resource information. Within the data set, I have 3 columns (Strength 1 , Strength 2 and Strength 3) where people are to indicate their strength from a given lists of strengths.

I would like to capture how many times a certain strength is picked and found in any of the 3 columns. How can I do it? (ie if the Strength is Customer Focus and it was chosen 4 times as the #1 choice, 3 times as the #2 choice and 1 time as the #3 choice, I want to know how to set up a function that will say Customer Focus was chosen 8 times)


Posted by Mark W. on April 18, 2001 11:45 AM

Diane, consider reorganizing your data into 2

{Strength, Rank}

Your existing organzation, {Strength 1, Stength 2...},
isn't in 1NF ("1st normal form") -- a fancy designation
used by database theorist. What you have now is a
database design with a "repeating group" -- 1, 2, 3.
PivotTables work more efficiently (and flexibly)
when your data is in 1NF.

Posted by Mark W. on April 18, 2001 11:53 AM

I should have...

...used your terminology when suggesting column labels.
Maybe, {Strength, Choice} would be more appropriate.