PCCUtech
asked on
Help counting distinct Records
Hi I have an excel table that looks something like this
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
Any ideas how I can accomplish this in Excel 2003?
Thanks
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
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
Any ideas how I can accomplish this in Excel 2003?
Thanks
http://office.microsoft.co m/en-us/ex cel-help/c ount-occur rences-of- values-or- unique-val ues-in-a-d ata-range- HP00305611 8.aspx
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
3-21-2012-3-30-37-PM.png
ASKER
@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?
@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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
I completely suck at array formulas LOL>
Thanks!