Count unique items in a set of data

[Injinia]

Hi,


I have a set of data that in the attachment below.


https://www.dropbox.com/s/6qb30fpcz7i1cbj/count%20unique%20values.xlsx


I would like to count how many people(column C) are available on each day. The data is on about 150k rows. I have done a pivot table for but I would appreciate help on how to solve the above.


Regards Injinia

 

[Faseeh]

Injina,


I think you have had the best solution. i.e pivot table.


Regards,

Faseeh

 

[Injinia]

But how do I count the number of people per day? one person could be recurring a number of times per day but I want the number of actual people on a given day.

 

[Faseeh]

In Column C, write this formula and drag down till end, (Just put in, this is with reference to your file), Mark column D as Count:

=COUNTIFS($B$2:B2,B2,$C$2:C2,C2)

Now Create a pivot table with:


> Date in row labels

> Name (as count) in Values

> Count in report filter


Now set the pivot table filter to 1. this will give you the result.


Faseeh

 

[Injinia]

Awesome!!!


Thanks alot for your help!


Regards, Injinia

 

[Faseeh]

Welcome,


Faseeh