unique values within groups

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

hans.verkade@gmail.com - 06 Feb 2007 11:05 GMT

Hello,

I've got a query in excel from which I need the unique values in this
query. I've managed to find the way to find the total unique numbers
from this query (with the formula SUM(IF(INTERVAL(RANGE;RANGE)>0;1)),
but I also want to find the unique numbers within a couple of groups
in this query. Does somebody knows how to do this?

For exampe, how do I find the unique values within the cities
Amsterdam and Rotterdam in the example beneath:

row 1 Amsterdam 443
row 2 Amsterdam 543
row 3 Amsterdam 643
row 4 Amsterdam 443
row 5 Rotterdam 543
row 6 Rotterdam 443
row 7 Rotterdam 443
row 8 Rotterdam 543

Amsterdam has 3 unique values, where Rotterdam has 2 unique values.

I hope somebody can help me.

Thanks in advance,

Hans

Reply to this Message

Mike - 06 Feb 2007 11:20 GMT

select your range of data, then

Data - Filter - Advanced filter and check unique records only

> Hello,
>
[quoted text clipped - 23 lines]
>
> Hans

Reply to this Message

verkade - 06 Feb 2007 11:33 GMT

> select your range of data, then
>
[quoted text clipped - 30 lines]
>
> - Tekst uit oorspronkelijk bericht weergeven -

Hi Mike,

I know that's a possibility, but I need it in a calculation and then
this possiblity isn't the right way. Thanks for answering so quickly
though.

Hans

Reply to this Message

Mike - 06 Feb 2007 12:16 GMT

If you want the number of records returned then use the COUNTA function on
the filtered list

For example if your list was in A1 to A9 with A1 as a label then with the
advanced filter applied =COUNTA(A2:A7)-1 will return 5 which is the total
number of unique records.

=COUNTA(A2:A4) will return 3 for Amsterdam.

=COUNTA(A6:A7) will return 2 for Rotterdam

> > select your range of data, then
> >
[quoted text clipped - 38 lines]
>
> Hans

Reply to this Message

verkade - 06 Feb 2007 12:47 GMT

> If you want the number of records returned then use the COUNTA function on
> the filtered list
[quoted text clipped - 51 lines]
>
> - Tekst uit oorspronkelijk bericht weergeven -

True, but this is a simplified example. This isn't enough if the query
has almost 50,000 rows, the list isn't sorted and the number of rows
gets more every week. I'm looking for an overall formula where I can
select the totale data range (a1:b9 in my example) to select the
unique numbers. I hope you know what I mean and want?

Gr,

Hans

Reply to this Message

Domenic - 06 Feb 2007 13:24 GMT

Assuming that A2:B9 contains the data, let D2 and D3 contain Amsterdam
and Rotterdam, then try the following formula which needs to be
confirmed with CONTROL+SHIFT+ENTER...

E2, copied down:

=SUM(IF(FREQUENCY(IF($A$2:$A$9=D2,$B$2:$B$9),IF($A$2:$A$9=D2,$B$2:$B$9)),
1))

Hope this helps!

> Hello,
>
[quoted text clipped - 23 lines]
>
> Hans

Reply to this Message