Hey Everyone,
I would really appreciate if someone can find me solution for this problem.
I am using a Pivot table and need to use it as making interconnected graphs and it's just easier.
However as when I want a count of something it counts all the rows in that column with the data but not just unique values.
So as to count all the distinct values in my table, first I tried to create a pivot data model which would give me a Distinct Count option but as there is a bug in Excel 2016, the misplaces the Data Model file and the workbook becomes corrupt.
Then I tried adding a column in the table which displays 1 for distinct and 0 for repeated values using the formula =IF(SUMPRODUCT(($A$6:$A6=A6)*($B$6:$B6=B6))>1,0,1). But this slows the whole report and as I am running queries it takes ages and most of the time have to just close the report as takes more than 8 hours.
Any solution with a formula or macro to add a value of displaying distinct values in a new column which can then be added to the pivot table.
Using SUM and countif have the same issues as SUMPRODUCT so if anything which will be quicker would be great. Anything between 10-15 min on a query run as a wait time is acceptable.
Looking forward hearing from you all.
I would really appreciate if someone can find me solution for this problem.
I am using a Pivot table and need to use it as making interconnected graphs and it's just easier.
However as when I want a count of something it counts all the rows in that column with the data but not just unique values.
So as to count all the distinct values in my table, first I tried to create a pivot data model which would give me a Distinct Count option but as there is a bug in Excel 2016, the misplaces the Data Model file and the workbook becomes corrupt.
Then I tried adding a column in the table which displays 1 for distinct and 0 for repeated values using the formula =IF(SUMPRODUCT(($A$6:$A6=A6)*($B$6:$B6=B6))>1,0,1). But this slows the whole report and as I am running queries it takes ages and most of the time have to just close the report as takes more than 8 hours.
Any solution with a formula or macro to add a value of displaying distinct values in a new column which can then be added to the pivot table.
Using SUM and countif have the same issues as SUMPRODUCT so if anything which will be quicker would be great. Anything between 10-15 min on a query run as a wait time is acceptable.
Looking forward hearing from you all.
