Count unique values by attribute

Hello, everyone.

I am using Excel 2010. I am working with data which I need to count unique values of one column from the unique values of another column. I have numerically-identified "events" in column A and numerically-identified "patches" in column B.

e.g.,

event	patch
001	001
001	002
001	003
002	004
...	...

I want to know how many *unique* patches occur for each *unique* event. Moreover, I want these counts to be printed in a column. In the above example, this would be

event	unique patch count
001	3
002	1
...	...

I know that there are formulas to count the total unique values of a single column, but I need to preserve the order of the counts by a second column (events). Thank you in advance for any help!

Originally Posted by marreco

hi.
try now..

This is close and I will certainly be able to use this as well, but I need the unique count of patches by unique events.

e.g.,

event	unique patch count
20000001	1
20000002	1
20000003	1
20000004	1
20000005	1
20000006	1
20000007	1
20000008	1
20000009	1
20000010	1
20000011	3
20000012	1
20000013	1
20000014	2
...	...

I need unique counts of patches for every event.

Originally Posted by jeffreybrown

Maybe this will work for you...

This is nearly what I am looking for. But this has essentially summed the number of records for each event. I need the *unique* number of patches for each event.

Originally Posted by marreco

hi.

try...

...
=CountIf(A:A;$A$2:A3)

This is similar to the formula jeffreybrown provided, but again, I need the counts of unique values in column B (patches) for unique values in column A (events).

Originally Posted by jeffreybrown

You have most likely surprised my formula ability, but why not use a pivot table?

Question, what is your answer for...?

Event
20000084

Patch
20000135

The data I am working with are spatial data. Each "patch" represents a polygon shape. Each "event" is a disturbance in a forest which is composed of patches. When I get the data from the program, it lists every patch as a record (i.e., row) and assigns each patch to an event. But I need the event records, not the patch records. So I need to summarize the number of unique patches for every event.

I have tried to use pivot tables, but I am only able to get as far as you have, which is to count the total number of patches by event rather than the number of unique patches by event. To my knowledge, pivot tables do not allow unique values to be summarized.

Originally Posted by jeffreybrown

Thanks for further explanation, but is there any way for you to answer the question for "Event" and "Patch" in question?

Is it 304?

Ah, sorry, I think I misunderstood you. For event 20000084, I count 55 unique patches (or 368 total records) using the formula:

Please Login or Register  to view this content.

So I guess I need a dynamic code (macro?) that applies the above formula to each unique event identifier (20000001 through 20000150 in the sample data I provided).

Originally Posted by jeffreybrown

How about this in column G

Notice I used some named ranges.

This works like a charm. Thank you so much for your persistence!

One more won't hurt then?

Originally Posted by WHER

One more won't hurt then?

Thanks, everyone! They all work very well!

Hello,

I did code an automatic unique within pivot for excel 2007. should work in 2010.
If you want to try:
http://lazyvba.blogspot.co.uk/2010/1...-to-count.html
have a good day