Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically extracting unique values
We need to extract the unique values from a range and have them copied to
another range. However, we are looking for a solution that will transparently keep the unique destination range values updated as the source range values change. Is this possible? Is there some formula or array formula that will extract the unique values from another range and keep them updated as they change? If not, is there a simple macro that could perform the job and be automatically triggered by cell edits to the source range? Our goal is to extract the unique account numbers from a large list of transactions and then summarize the transaction subtotals for any given account number. We are open to any ideas, thank you. |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically extracting unique values
On Fri, 11 Jan 2008 17:18:12 -0700, "Blue Max" wrote:
We need to extract the unique values from a range and have them copied to another range. However, we are looking for a solution that will transparently keep the unique destination range values updated as the source range values change. Is this possible? Is there some formula or array formula that will extract the unique values from another range and keep them updated as they change? If not, is there a simple macro that could perform the job and be automatically triggered by cell edits to the source range? Our goal is to extract the unique account numbers from a large list of transactions and then summarize the transaction subtotals for any given account number. We are open to any ideas, thank you. You could download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr/english/index.html Then you can use this formula: =INDEX(UNIQUEVALUES(Source_range,1),ROWS($1:1)) and then fill down as far (or further) than necessary. You can then use a SUMIF using the above cell for the criteria to get your subtotals. --ron |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically extracting unique values
Thank you, Ron, this looks like a very promising solution. However, I am
surprised that Microsoft has not provided a standard function of this nature. The function has many common uses in data analysis including data summarization, statistics, accounting, etcetera. Thank You ************** "Ron Rosenfeld" wrote in message ... On Fri, 11 Jan 2008 17:18:12 -0700, "Blue Max" wrote: We need to extract the unique values from a range and have them copied to another range. However, we are looking for a solution that will transparently keep the unique destination range values updated as the source range values change. Is this possible? Is there some formula or array formula that will extract the unique values from another range and keep them updated as they change? If not, is there a simple macro that could perform the job and be automatically triggered by cell edits to the source range? Our goal is to extract the unique account numbers from a large list of transactions and then summarize the transaction subtotals for any given account number. We are open to any ideas, thank you. You could download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr/english/index.html Then you can use this formula: =INDEX(UNIQUEVALUES(Source_range,1),ROWS($1:1)) and then fill down as far (or further) than necessary. You can then use a SUMIF using the above cell for the criteria to get your subtotals. --ron |
#4
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically extracting unique values
On Sat, 12 Jan 2008 12:39:03 -0700, "Blue Max" wrote:
Thank you, Ron, this looks like a very promising solution. However, I am surprised that Microsoft has not provided a standard function of this nature. The function has many common uses in data analysis including data summarization, statistics, accounting, etcetera. There are other solutions possible. There are complex formulas which can extract unique values from a list, but, so long as your strings are <256 characters, Longre's solution is simpler to implement. You could also set up a Pivot Table, but to make it completely automatic, you would also need to set up an event-triggered macro to refresh the pivot table when ever the source data changes. Best, --ron |
#5
Posted to microsoft.public.excel.programming, microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically extracting unique values
On Jan 13, 1:44 am, Ron Rosenfeld wrote:
On Sat, 12 Jan 2008 12:39:03 -0700, "Blue Max" wrote: Thank you, Ron, this looks like a very promising solution. However, I am surprised that Microsoft has not provided a standard function of this nature. The function has many common uses in data analysis including data summarization, statistics, accounting, etcetera. There are other solutions possible. There are complex formulas which can extract unique values from a list, but, so long as your strings are <256 characters, Longre's solution is simpler to implement. You could also set up a Pivot Table, but to make it completely automatic, you would also need to set up an event-triggered macro to refresh the pivot table when ever the source data changes. Best, --ron Hi, I have downloaded this extra functions package. I want to display unique values in the drop down list. But when I use UNIQUEVALUES() function in the data validation it gives me error "You may not use references to other worksheets or workbooks for Data Validation criteria." Help!! |
#6
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically extracting unique values
On Sun, 13 Jan 2008 22:53:10 -0800 (PST), ravisagar
wrote: Hi, I have downloaded this extra functions package. I want to display unique values in the drop down list. But when I use UNIQUEVALUES() function in the data validation it gives me error "You may not use references to other worksheets or workbooks for Data Validation criteria." Help!! That is a limitation of both data validation as well as conditional formatting. However, you can set up your list on your worksheet (in some hidden area, perhaps), using the UNIQUEVALUES function, and then refer to that list as the Source in your Data Validation settings. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting unique values from a list using VBA | Excel Discussion (Misc queries) | |||
Automatically extracting unique values | Excel Worksheet Functions | |||
Extracting unique values from live list | Excel Programming | |||
Populate a column by extracting unique values from another column? | Excel Worksheet Functions | |||
extracting unique values with a formula or with vba | Excel Programming |