Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Requesting Help with Counting in a Pivot but Excluding Duplicates
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!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Requesting Help with Counting in a Pivot but Excluding Duplicates
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 "Toria" wrote: 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!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Requesting Help with Counting in a Pivot but Excluding Duplicates
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 he http://www.contextures.com/xlPivot07.html#Unique Toria wrote: 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!! -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Requesting Help with Counting in a Pivot but Excluding Duplicates
=SUM(IF(Manager=C2,1/COUNTIF(CorpID,CorpID)))
ctrl+shift+enter, not just enter "Toria" wrote: 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!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
counting function but not double counting duplicates | Excel Worksheet Functions | |||
Counting non-blank cells in a column, excluding hidden rows | Excel Worksheet Functions | |||
Counting Cells, excluding those which have appeared before. | Excel Discussion (Misc queries) | |||
Formula to count cells between dates excluding duplicates | Excel Discussion (Misc queries) | |||
Count excluding Duplicates | Excel Worksheet Functions |