| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to create pivot table from existing pivot table in excel 2007 | Excel Discussion (Misc queries) | |||
| Filter lines with Pivot table and non pivot table columns | Charts and Charting in Excel | |||
| Filter lines with Pivot table and non Pivot table columns | Excel Discussion (Misc queries) | |||
| Filter lines containing pivot table and non pivot table data | Excel Worksheet Functions | |||
| How do I create a pivot table if the pivot table icon or menu ite. | Charts and Charting in Excel | |||
Linear Mode
