[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Excel 2010 Count Unique records from a list

Posted on 2014-01-26
4
Medium Priority
?
464 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

Industry Leaders: 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 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 article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

650 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