Distinct Count for Pivot Tables

[sixfourtykilo]

Hi, I'm using the trusted =IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1) formula but with large amounts of data, it's absolutely crushing system performance. I'm using the formula in a helper column of a source table to populate a column in a Pivot.

This is in Excel 2010.

Is there a VBA or alternate method I could/should be using to get my 1s and 0s?

Thanks!

 

[ChrisM]

Can you just use the "Count Of" pivot table function to exclude items with a count greater than 1?

 

[sixfourtykilo]

Can you just use the "Count Of" pivot table function to exclude items with a count greater than 1?

well i can't exclude them, i need to count them, just not all of them. if that makes sense.

i need to count each iteration of an entry (distinct). column B WILL have duplicates as there are a finite number of matches. column A will have an infinite number of results as that is the associated ticket number and MAY have duplicates.

so ex:
COL A | COL B | Helper
1234 | Title 1 | 1
1234 | Title 1 | 0
1235 | Title 1 | 1
1236 | Title 1 | 1
1236 | Title 1 | 0

Pivot Field Col A = 5 (count) but Col B (helper) "Title 1" = 3 and so on.

 

[Marcelo Branco]

I would use a different helper column and advanced filter

Something like this


[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr]
[tr][td]

1​

[/td][td]

Code​

[/td][td]

Title​

[/td][td]

Helper​

[/td][td] [/td][td]

Code​

[/td][td]

Title​

[/td][/tr]


[tr][td]

2​

[/td][td]

1234​

[/td][td]

Title 1​

[/td][td]

1234|Title 1​

[/td][td] [/td][td]

1234​

[/td][td]

Title 1​

[/td][/tr]


[tr][td]

3​

[/td][td]

1234​

[/td][td]

Title 1​

[/td][td]

1234|Title 1​

[/td][td] [/td][td]

1235​

[/td][td]

Title 1​

[/td][/tr]


[tr][td]

4​

[/td][td]

1235​

[/td][td]

Title 1​

[/td][td]

1235|Title 1​

[/td][td] [/td][td]

1236​

[/td][td]

Title 1​

[/td][/tr]


[tr][td]

5​

[/td][td]

1236​

[/td][td]

Title 1​

[/td][td]

1236|Title 1​

[/td][td] [/td][td] [/td][td] [/td][/tr]


[tr][td]

6​

[/td][td]

1236​

[/td][td]

Title 1​

[/td][td]

1236|Title 1​

[/td][td] [/td][td] [/td][td] [/td][/tr]
[/table]

Formula in C2 copied down
=A2&"|"&B2

Then you can use Advanced Filter
Put the headers Code Title in E1:F1

Select data range
Data > Advanced Filter
pick Copy to another location
Copy to: E1:F1

check Unique records only

Then you can use the new dataset to make the Pivot Table

Hope this helps

M.

 

[ChrisM]

It's tough to answer without seeing the full context of what you are trying to do. If you add only Column A and B to your pivot table Row section (nothing else in the pivot at all, just those two), it will give you a list of the results you are looking for. But the part I don't know is, is that enough to meet your needs? The total count of unique entries would be equal to the # of data rows in the pivot table.

 

[sixfourtykilo]

It's tough to answer without seeing the full context of what you are trying to do. If you add only Column A and B to your pivot table Row section (nothing else in the pivot at all, just those two), it will give you a list of the results you are looking for. But the part I don't know is, is that enough to meet your needs? The total count of unique entries would be equal to the # of data rows in the pivot table.

the formula I posted above accomplishes what I need - however it is performance intensive and is crippling all of the other formulas in the table. i simply need an alternative that gives me my ones and zeros.

Marcelo, creating another filtered table will not work. the existing pivot table needs to use the same source data.

 

[Marcelo Branco]

Marcelo, creating another filtered table will not work. the existing pivot table needs to use the same source data.

If the pairs Code-Title are grouped (or is possible to sort the data) you can use a simpler formula in helper column

C2
=IF(AND(A2=A1,B2=B1),0,1)
copy down

M.

 

[sixfourtykilo]

If the pairs Code-Title are grouped (or is possible to sort the data) you can use a simpler formula in helper column

C2
=IF(AND(A2=A1,B2=B1),0,1)
copy down

M.

the only problem i have with this formula is the dependency. this "dashboard" will be in the hands of others, so setting up the data to have a dependency on order seems like it's asking for trouble.

i'm not opposed to using a function, i just want to be smart about it (and not invoke external libraries).

the formula is so intensive that when i perform a copyfromrecordset, it takes over an hour to generate 71k rows of data.