Solved

Excel 2010 Count Unique records from a list

Posted on 2014-01-26
4
452 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.
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

718 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