Pivot Table Help
 

Excel 2003 on XP
I have a data set of over 45,000 rows and 150+ columns that contain the
certifications of engineers. I have several pivot tables that summaries this
data in various ways but I am having a problem in getting a certain result.
I wish to have a pivot table that "counts" the number of individual
engineers per certificate, but the system will only return the number of
certificates for this type, not the actual individuals that have this type of
certificate.
For example, suppose there are 150 type "A" certificates, and that only 10
engineers hold this type of certificate. I want the pivot to show the number
of individual engineers (10) against this certificate.
I believe DCOUNTA would work but the permutations are horrendous, how do I
get DCOUNTA into a pivot table?
Many thanks for your help.
DeanH

Pivot Table Help
 

Hi Dean,

Can you provide a sample sheet to work on?

Rgds

Prem

"DeanH" wrote:

Excel 2003 on XP
I have a data set of over 45,000 rows and 150+ columns that contain the
certifications of engineers. I have several pivot tables that summaries this
data in various ways but I am having a problem in getting a certain result.
I wish to have a pivot table that "counts" the number of individual
engineers per certificate, but the system will only return the number of
certificates for this type, not the actual individuals that have this type of
certificate.
For example, suppose there are 150 type "A" certificates, and that only 10
engineers hold this type of certificate. I want the pivot to show the number
of individual engineers (10) against this certificate.
I believe DCOUNTA would work but the permutations are horrendous, how do I
get DCOUNTA into a pivot table?
Many thanks for your help.
DeanH

Pivot Table Help
 

Many thanks for your reply and offer of help.
How would I send you this sample sheet?
DeanH

"Prem" wrote:

Hi Dean,

Can you provide a sample sheet to work on?

Rgds

Prem

"DeanH" wrote:

Excel 2003 on XP
I have a data set of over 45,000 rows and 150+ columns that contain the
certifications of engineers. I have several pivot tables that summaries this
data in various ways but I am having a problem in getting a certain result.
I wish to have a pivot table that "counts" the number of individual
engineers per certificate, but the system will only return the number of
certificates for this type, not the actual individuals that have this type of
certificate.
For example, suppose there are 150 type "A" certificates, and that only 10
engineers hold this type of certificate. I want the pivot to show the number
of individual engineers (10) against this certificate.
I believe DCOUNTA would work but the permutations are horrendous, how do I
get DCOUNTA into a pivot table?
Many thanks for your help.
DeanH

Pivot Table Help
 

A pivot table won't create a unique count, but there's a workaround he

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

With your layout that workaround may not be possible though.

DeanH wrote:
Excel 2003 on XP
I have a data set of over 45,000 rows and 150+ columns that contain the
certifications of engineers. I have several pivot tables that summaries this
data in various ways but I am having a problem in getting a certain result.
I wish to have a pivot table that "counts" the number of individual
engineers per certificate, but the system will only return the number of
certificates for this type, not the actual individuals that have this type of
certificate.
For example, suppose there are 150 type "A" certificates, and that only 10
engineers hold this type of certificate. I want the pivot to show the number
of individual engineers (10) against this certificate.
I believe DCOUNTA would work but the permutations are horrendous, how do I
get DCOUNTA into a pivot table?
Many thanks for your help.
DeanH


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Pivot Table Help
 

Fantastic, this works perfectly. After a little tinkering with my new pivot
table settings, I now have a perfect result.

Many thanks for your help.
DeanH

"Debra Dalgleish" wrote:

A pivot table won't create a unique count, but there's a workaround he

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

With your layout that workaround may not be possible though.

DeanH wrote:
Excel 2003 on XP
I have a data set of over 45,000 rows and 150+ columns that contain the
certifications of engineers. I have several pivot tables that summaries this
data in various ways but I am having a problem in getting a certain result.
I wish to have a pivot table that "counts" the number of individual
engineers per certificate, but the system will only return the number of
certificates for this type, not the actual individuals that have this type of
certificate.
For example, suppose there are 150 type "A" certificates, and that only 10
engineers hold this type of certificate. I want the pivot to show the number
of individual engineers (10) against this certificate.
I believe DCOUNTA would work but the permutations are horrendous, how do I
get DCOUNTA into a pivot table?
Many thanks for your help.
DeanH


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Pivot Table Help
 

One more option - add a column on the end of your data, populate it with "1"'s
and get the pivot table to count.
It may work!


"DeanH" wrote:

Fantastic, this works perfectly. After a little tinkering with my new pivot
table settings, I now have a perfect result.

Many thanks for your help.
DeanH

"Debra Dalgleish" wrote:

A pivot table won't create a unique count, but there's a workaround he

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

With your layout that workaround may not be possible though.

DeanH wrote:
Excel 2003 on XP
I have a data set of over 45,000 rows and 150+ columns that contain the
certifications of engineers. I have several pivot tables that summaries this
data in various ways but I am having a problem in getting a certain result.
I wish to have a pivot table that "counts" the number of individual
engineers per certificate, but the system will only return the number of
certificates for this type, not the actual individuals that have this type of
certificate.
For example, suppose there are 150 type "A" certificates, and that only 10
engineers hold this type of certificate. I want the pivot to show the number
of individual engineers (10) against this certificate.
I believe DCOUNTA would work but the permutations are horrendous, how do I
get DCOUNTA into a pivot table?
Many thanks for your help.
DeanH


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html