Solved

Help counting distinct Records

Posted on 2012-03-21
6
414 Views
Last Modified: 2012-08-14
Hi I have an excel table that looks something like this

ColA ColB
==== ====
1111 10-2011
1111 10-2011
2222 10-2011
2222 10-2011
3333 10-2011
2222 11-2011
1111 11-2011
2222 11-2011
4444 11-2011
1111 11-2011
2222 11-2011
3333 12-2011
3333 12-2011
2222 12-2011
6666 12-2011
1111 12-2011
1111 12-2011
3333 12-2011

Open in new window


What I need to do is could the distinct values in A for each grouping of B

So the out put needs to look like

10-2011 3
11-2011 3
12-2011 4

Open in new window


Any ideas how I can accomplish this in Excel 2003?

Thanks
0
Comment
Question by:PCCUtech
6 Comments
 
LVL 6

Expert Comment

by:netjgrnaut
ID: 37749402
0
 
LVL 6

Expert Comment

by:yawkey13
ID: 37749421
I think you are looking for the "Subtotal" functionality.  I have attached a screenshot on the configuration window.  Just highlight the two columns and click on the "Subtotal" icon in your toolbar.  I am running a later version of Excel, so your screen might look slightly different.
3-21-2012-3-30-37-PM.png
0
 

Author Comment

by:PCCUtech
ID: 37749539
@netjgrnaut:  Been there and that won't help because, as an example, 1111 needs to be considered unique 3 times (1 for each different bucket in column B)

@yawkey13: I don't see a subtotal icon in my tool bars.  I am using excel 2003 and just have the "subtotal" icon.  Nor do I find that in any of the other tool bars.  Perhaps you are using a different version of excel or have a specific add-in enabled?
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 37749550
Assuming you have the first month (10-2011) in D2 you can use this "array formula" in E2

=SUM(IF(FREQUENCY(IF(B$3:B$20=D2,IF(A$3:A$20<>"",MATCH(A$3:A$20,A$3:A$20,0))),ROW(A$3:A$20)-ROW(A$3)+1),1))

confirm with CTRL+SHIFT+ENTER and copy down

That works for any type of data in column A, if it will always be numeric data in that column then you can simplify to

=SUM(IF(FREQUENCY(IF(B$3:B$20=D2,A$3:A$20),A$3:A$20),1))

also confirmed with CTRL+SHIFT+ENTER

regards, barry

Edit: example now attached
count-distinct.xls
0
 
LVL 6

Expert Comment

by:yawkey13
ID: 37749726
0
 

Author Closing Comment

by:PCCUtech
ID: 37749739
Exactly what I was looking for... had to change it a bit for my exact sheet but this is it.

I completely suck at array formulas LOL>

Thanks!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
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…

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now