Simple Pivot Table to Count Unique Values

Question

This seems like a simple Pivot Table to learn with. I would like to do a count of unique values for a particular value I'm grouping on.

For instance, I have this:

ABC   123
ABC   123
ABC   123
DEF   456
DEF   567
DEF   456
DEF   456

What I want is a pivot table that shows me this:

ABC   1
DEF   2

The simple pivot table that I create just gives me this (a count of how many rows):

ABC   3
DEF   4  

But I want the number of unique values instead.

What I'm really trying to do is find out which values in the first column don't have the same value in the second column for all rows. In other words, "ABC" is "good", "DEF" is "bad"

I'm sure there is an easier way to do it but thought I'd give pivot table a try...

Answer 1

If you have the data sorted.. i suggest using the following formula

=IF(OR(A2<>A3,B2<>B3),1,0)

This is faster as it uses less cells to calculate.

Answer 2

See Debra Dalgleish's Count Unique Items

Answer 3

It is not necessary for the table to be sorted for the following formula to return a 1 for each unique value present.

assuming the table range for the data presented in the question is A1:B7 enter the following formula in Cell C1:

=IF(COUNTIF($B$1:$B1,B1)>1,0,COUNTIF($B$1:$B1,B1))

Copy that formula to all rows and the last row will contain:

=IF(COUNTIF($B$1:$B7,B7)>1,0,COUNTIF($B$1:$B7,B7))

This results in a 1 being returned the first time a record is found and 0 for all times afterwards.

Simply sum the column in your pivot table

Answer 4

I usually sort the data by the field I need to do the distinct count of then use IF(A2=A1,0,1); you get then get a 1 in the top row of each group of IDs. Simple and doesn't take any time to calculate on large datasets.

Answer 5

You can make an additional column to store the uniqueness, then sum that up in your pivot table.

What I mean is, cell C1 should always be 1. Cell C2 should contain the formula =IF(COUNTIF($A$1:$A1,$A2)*COUNTIF($B$1:$B1,$B2)>0,0,1). Copy this formula down so cell C3 would contain =IF(COUNTIF($A$1:$A2,$A3)*COUNTIF($B$1:$B2,$B3)>0,0,1) and so on.

If you have a header cell, you'll want to move these all down a row and your C3 formula should be =IF(COUNTIF($A$2:$A2,$A3)*COUNTIF($B$2:$B2,$B3)>0,0,1).

Answer 6

Siddharth's answer is terrific.

However, this technique can hit trouble when working with a large set of data (my computer froze up on 50,000 rows). Some less processor-intensive methods:

Single uniqueness check

1. Sort by the two columns (A, B in this example)

2. Use a formula that looks at less data

   =IF(SUMPRODUCT(($A2:$A3=A2)*($B2:$B3=B2))>1,0,1) 
   

Multiple uniqueness checks

If you need to check uniqueness in different columns, you can't rely on two sorts.

Instead,

1. Sort single column (A)

2. Add formula covering the maximum number of records for each grouping. If ABC might have 50 rows, the formula will be

   =IF(SUMPRODUCT(($A2:$A49=A2)*($B2:$B49=B2))>1,0,1)