?
Solved

Excel 2010 Count Unique records from a list

Posted on 2014-01-26
4
Medium Priority
?
458 Views
Last Modified: 2014-01-26
I have a list of records as follows:

1/24/2014  James Madison
1/24/2014  James Madison
1/24/2014  Abe Lincoln
1/24/2014  James Madison
1/24/2014  Abigail Adams
1/24/2014  Abigail Adams

I want the result in the Pivot Table  to be 1/24/2014      3      Unique name records.  What is the formula or summarize by values choice


THANKS
0
Comment
Question by:eyes59
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 18

Expert Comment

by:Steven Harris
ID: 39810422
1) Date is in Column A (with Header Date)
2) Name is in Column B (with Header Name)
3) Create a new Column C (with Header Unique Values)
4) In the new column, use the following formula and copy down:
=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1)

Open in new window


This will return the following:
using formula
Create your pivot table and you will get:
pivot
0
 

Author Comment

by:eyes59
ID: 39810455
What if I have multiple days.   I get the over unique count but not the unique count by each date.
0
 
LVL 18

Accepted Solution

by:
Steven Harris earned 2000 total points
ID: 39810472
In the same way, just changing Pivot Table Options.  I have added some highlighting to help distinguish what is going on:
multiple days
pivot as created
pivot - modified
Notice the changes made to the Report Fields (see arrows).
0
 
LVL 18

Expert Comment

by:Steven Harris
ID: 39810481
And just to help explain further how we are getting the info:

The formula is finding the unique values for a given condition.  In this instance, per date.  The number one (1) shows the first unique value for a given record. Zero (0) represents that a record with that value(s) has already been found and marked as one (1).

When you build the Pivot table and select all fields, this gives us the full base of the data:

pivot base data
In this case, 0 (null) is not a valid value, so it is not displayed.

We see all values with a 1, and each instance is added (Sum) to give us a total (which is the value you are looking for).
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question