Solved

Help counting distinct Records

Posted on 2012-03-21
6
422 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Technology Partners: 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

Outlook Free & Paid Tools
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

730 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