Pivot table
Posted by Steve on November 15, 2001 9:27 AM
Have some data with the same person's name on several job classifications by %. I need the pirov table to give me the names by title, but only 1 time. I.E., When I run it on titles, it gives me 4 draftsman when there's really only one but he's got 4 different functions. How to make the Pivot table provide only unique names?
Posted by Mark W. on November 15, 2001 10:36 AM
Steve, I'll describe a solution in it's most
simplest form. Suppose that you have the
following data list in A1:B7...
{"Employee","Task"
;"Mary","Check Email"
;"Larry","Make Coffee"
;"Susan","Sales Call"
;"Mary","Sales Call"
;"Susan","Write Report"
;"Susan","Check Email"}
...and you want to create a PivotTable to
count the Employees. If you're willing
to sort your list by 'Employee' you could
enter the formula, =(A2<>A1)+0, into
C2:C7, enter the label, "Unique Employee",
into C1, and setup 'Sum of Unique Employee'
in the DATA area of your PivotTable.
If you'd rather not insure that your data
list is always in sorted order you could
use the formula, =1/COUNTIF($A$2:$A$7,A2),
instead of =(A2<>A1)+0.
Now for the hitch... If your PivotTable is
to include any dimension other than 'Employee'
you must modify the 'Unique Employee' function
accordingly. For example, if you want to
count unique employees for the "Check Email"
task by placing 'Task' in the PAGE area you'll
get improper results.
Using the sorted approach you'd need to sort
on 'Employee' and 'Task', and establish an
'Unique Employee-Task' column using the
formula, =OR(A2<>A1,B2<>B1)+0. Since COUNTIF
can't be used for complex criteria the array
formula, {=1/SUM(($A$2:$A$7=A2)*($B$2:$B$7=B2))},
would be needed.
As you can see... the more dimensions that you
have in your data set... the more complex these
formulas become! Of course another solution
would be to store your data in 3rd Normal Form
(3NF) in a RDBMS or as a ODBC compliant text file
and use SQL to return distinct counts.
