Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula - Analyse range, return unique value
Hi there,
I used to have a formula for this, but a computer swap has left me without any of my saved excel formulas. If someone can help me out, I would appreciate it. I need a formula where it can essentially look at a range, dynamically, and then return the unique values within that range. This formula will be present beside a query being refreshed from our server, so it cannot be a 'drag down' type formula and must coincide to its row number. But perhaps Im asking for too much - I'll take what I can get. Essentially, return the value only if the data is being returned the first time: Data - - - Returned 101 - - - 101 102 - - - 102 101 - - - 103 - - - 103 104 - - - 104 102 - - - 101 105 - - - 105 Any help is greatly appreciated!!! I'm starting my furious searching (this has already gone off to a VP with an error... woops) and will report back with any findings - in the meantime, any knowledge that can be shared would be great :) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula - Analyse range, return unique value
On Feb 20, 12:28 pm, "S Davis" wrote:
Hi there, I used to have a formula for this, but a computer swap has left me without any of my saved excel formulas. If someone can help me out, I would appreciate it. I need a formula where it can essentially look at a range, dynamically, and then return the unique values within that range. This formula will be present beside a query being refreshed from our server, so it cannot be a 'drag down' type formula and must coincide to its row number. But perhaps Im asking for too much - I'll take what I can get. Essentially, return the value only if the data is being returned the first time: Data - - - Returned 101 - - - 101 102 - - - 102 101 - - - 103 - - - 103 104 - - - 104 102 - - - 101 105 - - - 105 Any help is greatly appreciated!!! I'm starting my furious searching (this has already gone off to a VP with an error... woops) and will report back with any findings - in the meantime, any knowledge that can be shared would be great :) Also totally acceptable would be the following: Data - - - Returned 101 - - - 1 102 - - - 1 101 - - - 2 103 - - - 1 104 - - - 1 102 - - - 2 101 - - - 3 105 - - - 1 .... which I could then just write a formula to read only '1's. Purpose of this list is to count the number of entities in a list, but some of them have multiple entries. Thanks again, -SD |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula - Analyse range, return unique value
See
http://www.cpearson.com/excel/duplic...tractingUnique HTH "S Davis" wrote: Hi there, I used to have a formula for this, but a computer swap has left me without any of my saved excel formulas. If someone can help me out, I would appreciate it. I need a formula where it can essentially look at a range, dynamically, and then return the unique values within that range. This formula will be present beside a query being refreshed from our server, so it cannot be a 'drag down' type formula and must coincide to its row number. But perhaps Im asking for too much - I'll take what I can get. Essentially, return the value only if the data is being returned the first time: Data - - - Returned 101 - - - 101 102 - - - 102 101 - - - 103 - - - 103 104 - - - 104 102 - - - 101 105 - - - 105 Any help is greatly appreciated!!! I'm starting my furious searching (this has already gone off to a VP with an error... woops) and will report back with any findings - in the meantime, any knowledge that can be shared would be great :) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula - Analyse range, return unique value
Purpose of this list is to count the number of entities in a list,
but some of them have multiple entries. If you want the count of unique entries try one of these: Since your sample data is numeric: =COUNT(1/FREQUENCY(A1:A10,A1:A10)) This one is a generic formula for counting uniques: =SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&"")) Biff "S Davis" wrote in message oups.com... On Feb 20, 12:28 pm, "S Davis" wrote: Hi there, I used to have a formula for this, but a computer swap has left me without any of my saved excel formulas. If someone can help me out, I would appreciate it. I need a formula where it can essentially look at a range, dynamically, and then return the unique values within that range. This formula will be present beside a query being refreshed from our server, so it cannot be a 'drag down' type formula and must coincide to its row number. But perhaps Im asking for too much - I'll take what I can get. Essentially, return the value only if the data is being returned the first time: Data - - - Returned 101 - - - 101 102 - - - 102 101 - - - 103 - - - 103 104 - - - 104 102 - - - 101 105 - - - 105 Any help is greatly appreciated!!! I'm starting my furious searching (this has already gone off to a VP with an error... woops) and will report back with any findings - in the meantime, any knowledge that can be shared would be great :) Also totally acceptable would be the following: Data - - - Returned 101 - - - 1 102 - - - 1 101 - - - 2 103 - - - 1 104 - - - 1 102 - - - 2 101 - - - 3 105 - - - 1 ... which I could then just write a formula to read only '1's. Purpose of this list is to count the number of entities in a list, but some of them have multiple entries. Thanks again, -SD |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula - Analyse range, return unique value
On Feb 20, 12:55 pm, Toppers
wrote: See http://www.cpearson.com/excel/duplic...tractingUnique HTH "S Davis" wrote: Hi there, I used to have a formula for this, but a computer swap has left me without any of my saved excel formulas. If someone can help me out, I would appreciate it. I need a formula where it can essentially look at a range, dynamically, and then return the unique values within that range. This formula will be present beside a query being refreshed from our server, so it cannot be a 'drag down' type formula and must coincide to its row number. But perhaps Im asking for too much - I'll take what I can get. Essentially, return the value only if the data is being returned the first time: Data - - - Returned 101 - - - 101 102 - - - 102 101 - - - 103 - - - 103 104 - - - 104 102 - - - 101 105 - - - 105 Any help is greatly appreciated!!! I'm starting my furious searching (this has already gone off to a VP with an error... woops) and will report back with any findings - in the meantime, any knowledge that can be shared would be great :)- Hide quoted text - - Show quoted text - This worked perfectly, thank you ! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Formula based Cell Content Return Unique Consecutive Duplicate Values | Excel Worksheet Functions | |||
formula to look up and return smallest date from a range of dates | Excel Worksheet Functions | |||
Excel - return a picture or range rows as the result of a formula | Excel Worksheet Functions | |||
Return a unique value | Excel Worksheet Functions | |||
Formula to return ADDRESS of cell in range that meets criteria | Excel Worksheet Functions |