unique values within groups

[hans.verkade]

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

 

[Guest]

select your range of data, then

Data - Filter - Advanced filter and check unique records only

 

[verkade]

select your range of data, then

Data - Filter - Advanced filter and check unique records only












- 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

 

[Guest]

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

 

[verkade]

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








- 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

 

[Domenic]

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!