Requesting Help with Counting in a Pivot but Excluding Duplicates

[Toria]

Hello,

I have column A with corporate IDs, column B with BAN numbers and column C
with managers (33,510 rows total). All the BANs will be different but
sometimes the corp ID can be the same. I need to be able to do a pivot table
by each manager (50 managers) and count the corp IDs but NOT count any
duplicate corp IDs. For example, for the below sample, the pivot would show
1 for Smith, 1 for Rogers (duplicate corp ID) and 2 for Brown (2 different
corp IDs).

A Corp ID B BAN C Manager

1 GDCCW_USAF_3060_P 431030123 J. Smith
2 GABOC_GS2_3060_P 0624210300 V. Rogers
3 GABOC_GS2_3060_P 431030123 V. Rogers
4 799791314_6021_P 799791314 K. Brown
5 411781312_6021_P 411781312 K. Brown

Thank you!!

 

[Tom Hutchins]

Why not build the pivot table from a copy of your worksheet where you have
removed the duplicate corp ID records?
- sort the data by Corp ID and Manager
- in column D, in the first row of data, enter this formula:
=IF(A2&C2=A1&C1,"Dupe","")
- copy the formula down through all the rows of data
- recalc
- copy & paste column D in place as values
- select all data and sort by column D
- delete all rows with "Dupe" in column D
- make your pivot table

Hope this helps,

Hutch

 

[Debra Dalgleish]

There's no function in a pivot table that will count unique items. As a
workaround, you could add a field to the source data, to calculate the
count of operators, and add that field to the pivot table. There's an
example here:

http://www.contextures.com/xlPivot07.html#Unique

 

[Teethless mama]

=SUM(IF(Manager=C2,1/COUNTIF(CorpID,CorpID)))

ctrl+shift+enter, not just enter