Solved

Excel 2010 Count Unique records from a list

Posted on 2014-01-26
4
451 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 500 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

734 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