Hi, I have a problem with Excel and I'm writing hoping you can help me.
I have Excel 2010 (with Excel 2013 problem seems to be resolved), with a large table of data, and a pivot on this table. This pivot contains page fields, and in value fields I have to count infos of a column "NDC". But I have to count data with DISTINCT values.
So I googled a lot, and I found a remedy: I added a column in the initial table of data, so that if a value appears for the first time in a row, in the corresponding column added I have a "1"; if that value is repeated in consequent rows, I have a "0".
For example:
NDC ADDED COLUMN
1 1
1 0
2 1
3 1
In my pivot, than I got the sum of the added column, obtaining distinct count of "NDC".
However, this is a partial remedy, because, if I have page fields that can FILTER the original table data, sum of the added column is no more a correct total.
In fact, suppose this situation:
NDC A.C. F
1 1 S
1 0 N
2 1 N
3 1 N
If in Page Fields I have the field "F", and I choose to view only values "N", then my pivot counts 0 for NDC "1", 1 for NDC "2", 1 for NDC "3", and totals 2 instead of 3.
How can I resolve this problem? There is a workaround (using macro, or another more complex formula)?
Thank you and sorry for my english, I hope you understand me.
I have Excel 2010 (with Excel 2013 problem seems to be resolved), with a large table of data, and a pivot on this table. This pivot contains page fields, and in value fields I have to count infos of a column "NDC". But I have to count data with DISTINCT values.
So I googled a lot, and I found a remedy: I added a column in the initial table of data, so that if a value appears for the first time in a row, in the corresponding column added I have a "1"; if that value is repeated in consequent rows, I have a "0".
For example:
NDC ADDED COLUMN
1 1
1 0
2 1
3 1
In my pivot, than I got the sum of the added column, obtaining distinct count of "NDC".
However, this is a partial remedy, because, if I have page fields that can FILTER the original table data, sum of the added column is no more a correct total.
In fact, suppose this situation:
NDC A.C. F
1 1 S
1 0 N
2 1 N
3 1 N
If in Page Fields I have the field "F", and I choose to view only values "N", then my pivot counts 0 for NDC "1", 1 for NDC "2", 1 for NDC "3", and totals 2 instead of 3.
How can I resolve this problem? There is a workaround (using macro, or another more complex formula)?
Thank you and sorry for my english, I hope you understand me.
