# 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...

;"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&LT;&GT;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),

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"
get improper results.

Using the sorted approach you'd need to sort
on 'Employee' and 'Task', and establish an