Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
unique values within groups
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
unique values within groups
select your range of data, then
Data - Filter - Advanced filter and check unique records only " wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
unique values within groups
On 6 feb, 12:20, Mike wrote:
select your range of data, then Data - Filter - Advanced filter and check unique records only " wrote: 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- Tekst uit oorspronkelijk bericht niet weergeven - - 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
unique values within groups
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" wrote: On 6 feb, 12:20, Mike wrote: select your range of data, then Data - Filter - Advanced filter and check unique records only " wrote: 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- Tekst uit oorspronkelijk bericht niet weergeven - - 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
unique values within groups
On 6 feb, 13:16, Mike wrote:
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" wrote: On 6 feb, 12:20, Mike wrote: select your range of data, then Data - Filter - Advanced filter and check unique records only " wrote: 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- Tekst uit oorspronkelijk bericht niet weergeven - - 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- Tekst uit oorspronkelijk bericht niet weergeven - - 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Unique Values | Excel Worksheet Functions | |||
Summing unique values | Excel Worksheet Functions | |||
Count Unique Values with Multiple Criteria | Excel Worksheet Functions | |||
Unique values | Excel Worksheet Functions | |||
Count unique values and create list based on these values | Excel Worksheet Functions |