| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot table: how to count unique? need help!
this a table similar to the one I really have
serialnb label 111 a 111 b 222 a I want to create a pivot that shows how many *unique* serials exist, and also per label, etc. Doing the "usual" pivot I get the following Count of serialnb serialnb label Total 111 a 1 b 1 222 a 1 Grand Total 3 How can I show the results I want? (should be 2 for unique serial numbers) Many thanks! luis |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pivot table: how to count unique? need help!
luiss wrote:
this a table similar to the one I really have serialnb label 111 a 111 b 222 a I want to create a pivot that shows how many *unique* serials exist, and also per label, etc. Doing the "usual" pivot I get the following Count of serialnb serialnb label Total 111 a 1 b 1 222 a 1 Grand Total 3 How can I show the results I want? (should be 2 for unique serial numbers) Many thanks! luis Drop serialnb into ROW area Drop label into ROW area Drop label into DATA area That gives me serialnb label Total 111 a 1 b 1 111 Total 2 222 a 1 222 Total 1 Grand Total 3 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pivot table: how to count unique? need help!
Hi Paul,
thanks. That doesn't give the right answer though... i.e. would still see that in total there are 3 devices (those are serial numbers of the devices) but it is not true... there are 2 devices with distinct serial numbers and happen that one has 2 lables attached and the other one... luis "Paul Lautman" wrote: luiss wrote: this a table similar to the one I really have serialnb label 111 a 111 b 222 a I want to create a pivot that shows how many *unique* serials exist, and also per label, etc. Doing the "usual" pivot I get the following Count of serialnb serialnb label Total 111 a 1 b 1 222 a 1 Grand Total 3 How can I show the results I want? (should be 2 for unique serial numbers) Many thanks! luis Drop serialnb into ROW area Drop label into ROW area Drop label into DATA area That gives me serialnb label Total 111 a 1 b 1 111 Total 2 222 a 1 222 Total 1 Grand Total 3 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pivot table: how to count unique? need help!
luiss wrote:
Hi Paul, thanks. That doesn't give the right answer though... i.e. would still see that in total there are 3 devices (those are serial numbers of the devices) but it is not true... there are 2 devices with distinct serial numbers and happen that one has 2 lables attached and the other one... luis "Paul Lautman" wrote: luiss wrote: this a table similar to the one I really have serialnb label 111 a 111 b 222 a I want to create a pivot that shows how many *unique* serials exist, and also per label, etc. Doing the "usual" pivot I get the following Count of serialnb serialnb label Total 111 a 1 b 1 222 a 1 Grand Total 3 How can I show the results I want? (should be 2 for unique serial numbers) Many thanks! luis Drop serialnb into ROW area Drop label into ROW area Drop label into DATA area That gives me serialnb label Total 111 a 1 b 1 111 Total 2 222 a 1 222 Total 1 Grand Total 3 I can offer 2 methods of doing this. One uses 2 tables, with the second table based on the resoults of the first one. The other uses a simple COUNTA() function to supply the result. Both methds are a bit difficult to explain, so I'll pop an example on the web for you. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pivot table: how to count unique? need help!
"Paul Lautman" wrote: luiss wrote: Hi Paul, thanks. That doesn't give the right answer though... i.e. would still see that in total there are 3 devices (those are serial numbers of the devices) but it is not true... there are 2 devices with distinct serial numbers and happen that one has 2 lables attached and the other one... luis "Paul Lautman" wrote: luiss wrote: this a table similar to the one I really have serialnb label 111 a 111 b 222 a I want to create a pivot that shows how many *unique* serials exist, and also per label, etc. Doing the "usual" pivot I get the following Count of serialnb serialnb label Total 111 a 1 b 1 222 a 1 Grand Total 3 How can I show the results I want? (should be 2 for unique serial numbers) Many thanks! luis Drop serialnb into ROW area Drop label into ROW area Drop label into DATA area That gives me serialnb label Total 111 a 1 b 1 111 Total 2 222 a 1 222 Total 1 Grand Total 3 I can offer 2 methods of doing this. One uses 2 tables, with the second table based on the resoults of the first one. The other uses a simple COUNTA() function to supply the result. Both methds are a bit difficult to explain, so I'll pop an example on the web for you. Hi Paul, ok. thanks. please let me know where I can see that. I used COUNTA... but not a good solution as I want it to dinamically change when i change the variables of the pivot. thanks luis |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pivot table: how to count unique? need help!
luiss wrote:
"Paul Lautman" wrote: luiss wrote: Hi Paul, thanks. That doesn't give the right answer though... i.e. would still see that in total there are 3 devices (those are serial numbers of the devices) but it is not true... there are 2 devices with distinct serial numbers and happen that one has 2 lables attached and the other one... luis "Paul Lautman" wrote: luiss wrote: this a table similar to the one I really have serialnb label 111 a 111 b 222 a I want to create a pivot that shows how many *unique* serials exist, and also per label, etc. Doing the "usual" pivot I get the following Count of serialnb serialnb label Total 111 a 1 b 1 222 a 1 Grand Total 3 How can I show the results I want? (should be 2 for unique serial numbers) Many thanks! luis Drop serialnb into ROW area Drop label into ROW area Drop label into DATA area That gives me serialnb label Total 111 a 1 b 1 111 Total 2 222 a 1 222 Total 1 Grand Total 3 I can offer 2 methods of doing this. One uses 2 tables, with the second table based on the resoults of the first one. The other uses a simple COUNTA() function to supply the result. Both methds are a bit difficult to explain, so I'll pop an example on the web for you. Hi Paul, ok. thanks. please let me know where I can see that. I used COUNTA... but not a good solution as I want it to dinamically change when i change the variables of the pivot. thanks luis See http://cjoint.com/?gouq44q3Mc The COUNTA call will automatically change to take account of varying numbers of serials |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pivot table: how to count unique? need help!
You can add a field to the source data, and use it to calculate the
unique count in the pivot table. There's an example he http://www.contextures.com/xlPivot07.html#Unique luiss wrote: this a table similar to the one I really have serialnb label 111 a 111 b 222 a I want to create a pivot that shows how many *unique* serials exist, and also per label, etc. Doing the "usual" pivot I get the following Count of serialnb serialnb label Total 111 a 1 b 1 222 a 1 Grand Total 3 How can I show the results I want? (should be 2 for unique serial numbers) Many thanks! luis -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pivot table: how to count unique? need help!
PERFECT!!!!!
that is exactly what I had asked! great and excellent site! thanks Debra luis "Debra Dalgleish" wrote: You can add a field to the source data, and use it to calculate the unique count in the pivot table. There's an example he http://www.contextures.com/xlPivot07.html#Unique luiss wrote: this a table similar to the one I really have serialnb label 111 a 111 b 222 a I want to create a pivot that shows how many *unique* serials exist, and also per label, etc. Doing the "usual" pivot I get the following Count of serialnb serialnb label Total 111 a 1 b 1 222 a 1 Grand Total 3 How can I show the results I want? (should be 2 for unique serial numbers) Many thanks! luis -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pivot table: how to count unique? need help!
You're welcome! Thanks for letting me know that it helped.
luiss wrote: PERFECT!!!!! that is exactly what I had asked! great and excellent site! thanks Debra luis "Debra Dalgleish" wrote: You can add a field to the source data, and use it to calculate the unique count in the pivot table. There's an example he http://www.contextures.com/xlPivot07.html#Unique luiss wrote: this a table similar to the one I really have serialnb label 111 a 111 b 222 a I want to create a pivot that shows how many *unique* serials exist, and also per label, etc. Doing the "usual" pivot I get the following Count of serialnb serialnb label Total 111 a 1 b 1 222 a 1 Grand Total 3 How can I show the results I want? (should be 2 for unique serial numbers) Many thanks! luis -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2k Pivot Table refresh scenario | Excel Discussion (Misc queries) | |||
| How do I use a pivot table to get an average count? | Excel Discussion (Misc queries) | |||
| pivot table | Excel Discussion (Misc queries) | |||
| pivot table created from another pivot table | Excel Worksheet Functions | |||
| Count Unique in Pivot Table | Excel Discussion (Misc queries) | |||
Hybrid Mode
