Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning an array of unique values?
Is there anyway, using a formula, to return an array of unique values
extracted from a larger array that has duplicate values. The resulting array would be used for further calculations in the rest of the formula? Likewise, is there anyway to use the values (true, false) in one array to determine which values are selected out of another array for creating a resulting array to be used for further calculations in the formula? Can an array be sorted within a formula before using it in the balance of the formula? I am able to generate an array including all the values I need, but I cannot get rid of the duplicate values in that array. Thank you for any help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning an array of unique values?
Hello,
You can use my UDF lfreq, for example: http://www.sulprobil.com/html/listfreq.html Regards, Bernd |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning an array of unique values?
Blue Max wrote:
Is there anyway, using a formula, to return an array of unique values extracted from a larger array that has duplicate values. The resulting array would be used for further calculations in the rest of the formula? Likewise, is there anyway to use the values (true, false) in one array to determine which values are selected out of another array for creating a resulting array to be used for further calculations in the formula? Can an array be sorted within a formula before using it in the balance of the formula? I am able to generate an array including all the values I need, but I cannot get rid of the duplicate values in that array. Thank you for any help. It might help us to help you if you post the formula; that way we can see how the array is to be used for further calculations. The answers to your 3 questions are yes, yes, yes. Alan Beban |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning an array of unique values?
Thank you, Bernd. It looks like this function holds promise. Is the
function returning both a list of values and their frequency? In our case, we only need the list of unique values or text strings. How is your formula eliminating the duplicate values and coming up with a list of the unique values to associate with your frequency counts? Thank You Blue ****************** "Bernd P" wrote in message ... Hello, You can use my UDF lfreq, for example: http://www.sulprobil.com/html/listfreq.html Regards, Bernd |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning an array of unique values?
"Blue Max" wrote...
Is there anyway, using a formula, to return an array of unique values extracted from a larger array that has duplicate values. The resulting array would be used for further calculations in the rest of the formula? .... The best approach depends on the final result you want. In general, for all the things you seem to want to do, you should consider downloading and installing Laurent Longre's MOREFUNC.XLL add-in, avaialble from http://xcell05.free.fr/morefunc/english/index.htm Read its help file about its UNIQUEVALUES, HSORT, VSORT and ARRAY.FILTER functions. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning an array of unique values?
Thank you for the reply, Alan. My questions were intended to be general so
that I could use the specific techniques for modifying arrays in different situations. Nevertheless, here is an example if it will help explain your 'Yes' answers to our three questions: The sample formula {=IF(FREQUENCY(MATCH(A1:A8,A1:A8,0),MATCH(A1:A8,A1 :A8,0))0,INDEX(A1:A8,MATCH(A1:A8,A1:A8,0)),"")} is entered as an array formula in the range A10:A17. The source range A1:A8 in the formula is simply a test column of 8 cells with the entries Banana, Apple, Apple, Banana, Orange, Pineapple, Orange, and Nut. The formula returns a listing of unique fruits to the destination range A10:A17 of Banana, Apple, "", "", Orange, Pineapple, "", Nut. As you can see, the formula successfully extracts the unique values of the source range and keeps them properly updated in the destination range as values are modified in the source range. The problem here is that the resulting destination list is interspersed with blank cells. We want to eliminate the blank cells from the destination range and list the unique values sequentially at the top of the range. At some point we my also wish to have the formula sort the resulting unique values before placing them in the destination range of cells. This technique is often useful in accounting where the user needs to identify all the unique General Ledger Accounts in a very long list of detail transactions which are repeatedly associated with the same set of account numbers. After identifying the unique accounts, then the user can summarize all of the transaction amounts by account number. Obviously, there are hundreds of other similar examples where the user desires to summarize a subset of data from a larger population. As you indicate that all three of our tasks could be performed as described, we hope that the examples will help you give us a little more insight. As you probably noticed, all our questions dealt with modifying an array within a formula, so that it could be further processed within the formula before a result was output to the destiation cells. Obviously, we are struggling to find standard functions or techniques that allow us to modify these arrays within the formula. We are also interested in knowing if a user could link the original database to a smaller pivot table, beneath the original data, designed to resummarize the original data in the desired format. Thanks, Blue ********** "Alan Beban" wrote in message ... Blue Max wrote: Is there anyway, using a formula, to return an array of unique values extracted from a larger array that has duplicate values. The resulting array would be used for further calculations in the rest of the formula? Likewise, is there anyway to use the values (true, false) in one array to determine which values are selected out of another array for creating a resulting array to be used for further calculations in the formula? Can an array be sorted within a formula before using it in the balance of the formula? I am able to generate an array including all the values I need, but I cannot get rid of the duplicate values in that array. Thank you for any help. It might help us to help you if you post the formula; that way we can see how the array is to be used for further calculations. The answers to your 3 questions are yes, yes, yes. Alan Beban |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning an array of unique values?
Thank you Harlan. At this point I am looking into these functions. I would
obviously much rather be able to perform the task with standard Excel functions, for reasons or portability, but may have to resort to outside custom functions as you suggest. Thanks, Blue ********* "Harlan Grove" wrote in message ... "Blue Max" wrote... Is there anyway, using a formula, to return an array of unique values extracted from a larger array that has duplicate values. The resulting array would be used for further calculations in the rest of the formula? ... The best approach depends on the final result you want. In general, for all the things you seem to want to do, you should consider downloading and installing Laurent Longre's MOREFUNC.XLL add-in, avaialble from http://xcell05.free.fr/morefunc/english/index.htm Read its help file about its UNIQUEVALUES, HSORT, VSORT and ARRAY.FILTER functions. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning an array of unique values?
"Blue Max" wrote...
.... . . . Nevertheless, here is an example if it will help explain your 'Yes' answers to our three questions: The sample formula {=IF(FREQUENCY(MATCH(A1:A8,A1:A8,0),MATCH(A1:A8,A 1:A8,0))0, INDEX(A1:A8,MATCH(A1:A8,A1:A8,0)),"")} is entered as an array formula in the range A10:A17. The source range A1:A8 in the formula is simply a test column of 8 cells with the entries Banana, Apple, Apple, Banana, Orange, Pineapple, Orange, and Nut. The formula returns a listing of unique fruits to the destination range A10:A17 of Banana, Apple, "", "", Orange, Pineapple, "", Nut. You could make this more efficient by using the array formula =IF(MATCH(A1:A8,A1:A8,0)=ROW(A1:A8)-MIN(ROW(A1:A8))+1,A1:A8,"") As you can see, the formula successfully extracts the unique values of the source range and keeps them properly updated in the destination range as values are modified in the source range. The problem here is that the resulting destination list is interspersed with blank cells. We want to eliminate the blank cells from the destination range and list the unique values sequentially at the top of the range. At some point we my also wish to have the formula sort the resulting unique values before placing them in the destination range of cells. The destination range COULD return as many items as the source range if all items in the source range were distinct. So you'll need to make due with formulas that evaluate to "" or #N/A for duplicate items in the source list, but those values could appear below the distinct values. The most efficient way to load the distinct values into another range would be with formulas like these (which also sort). C1 [array formula]: =INDEX($A$1:$A$8,MATCH(0,COUNTIF($A$1:$A$8,"<"&$A$ 1:$A$8),0)) C2 [array formula]: =IF(COUNTIF($A$1:$A$8,""&C1),INDEX($A$1:$A$8,MATC H(COUNTIF($A$1:$A$8, "<="&C1),COUNTIF($A$1:$A$8,"<"&$A$1:$A$8),0)), "") Fill C2 down into C3:C8. Given your data in A1:A8, this fills C1:C8 with {"Apple";"Banana";"Nut";"Orange";"Pineapple";"";"" ;""}. This technique is often useful in accounting where the user needs to identify all the unique General Ledger Accounts in a very long list of detail transactions which are repeatedly associated with the same set of account numbers. . . . .... In that case, it'd be more more efficient to use an advanced filter, copying only the unique items in the source range to the destination range followed by sorting the filtered, unique results. Or you could use a pivot table to summarize fields by general ledger number if your general ledger data were in a table. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning an array of unique values?
Blue Max wrote:
Is there anyway, using a formula, to return an array of unique values extracted from a larger array that has duplicate values. The resulting array would be used for further calculations in the rest of the formula? Likewise, is there anyway to use the values (true, false) in one array to determine which values are selected out of another array for creating a resulting array to be used for further calculations in the formula? Can an array be sorted within a formula before using it in the balance of the formula? I am able to generate an array including all the values I need, but I cannot get rid of the duplicate values in that array. Thank you for any help. If the functions in the freely downloadable file at http://home.pacbell.net/beban are available to your workbook, you could use something like the following to operate on a single column range: Function SortedUniques(inputArray) z = ArrayUniques(OneD(Application.Transpose(inputArray )), , "1horiz") QuickSort z SortedUniques = z End Function array entered into your output range. Alan Beban |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning an array of unique values?
Alan,
Thank you very much for the suggestions and other resources. *********************** "Alan Beban" wrote in message ... Blue Max wrote: Is there anyway, using a formula, to return an array of unique values extracted from a larger array that has duplicate values. The resulting array would be used for further calculations in the rest of the formula? Likewise, is there anyway to use the values (true, false) in one array to determine which values are selected out of another array for creating a resulting array to be used for further calculations in the formula? Can an array be sorted within a formula before using it in the balance of the formula? I am able to generate an array including all the values I need, but I cannot get rid of the duplicate values in that array. Thank you for any help. If the functions in the freely downloadable file at http://home.pacbell.net/beban are available to your workbook, you could use something like the following to operate on a single column range: Function SortedUniques(inputArray) z = ArrayUniques(OneD(Application.Transpose(inputArray )), , "1horiz") QuickSort z SortedUniques = z End Function array entered into your output range. Alan Beban |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning an array of unique values?
Brilliant, Harlan! Your examples opened up a whole new realm of
possibilites. Your alternate formula was much more efficient than the one derived from the Microsoft help examples. Your samples accomplished exactly what we needed, albeit with a few more formulas than anticipated. Now I am wondering if there might be away to consolidate all of this into a single formula somehow? If I make any progress I will certainly share my findings. Thank you for the caution regarding the fact that the output could potentially equal the size of the original database, if every entry were unique. We had anticipated this issue and are designing this worksheet to prevent any such problem. As to using these formulas in a complex accounting environment, we agree with your observation. We would never implement these formulas in a complex accounting environment. We have accounting packages that are very adequate to our needs. Nevertheless, these formulas are perfect for small worksheets that help prepare our data for input into a formal accounting system. In this case, we are designing an invoice distribution worksheet that will allow us to quickly distribute line item charges on a vendor invoice to specific accounts including their prorated portions of tax, shipping, handling, and other charges. While this represents a departure from normal accounting practices, it meets the needs of a client who desires to reflect expense disbursements that include their prorata share of other related costs. Thanks again, Blue ************************ "Harlan Grove" wrote in message ... "Blue Max" wrote... ... . . . Nevertheless, here is an example if it will help explain your 'Yes' answers to our three questions: The sample formula {=IF(FREQUENCY(MATCH(A1:A8,A1:A8,0),MATCH(A1:A8, A1:A8,0))0, INDEX(A1:A8,MATCH(A1:A8,A1:A8,0)),"")} is entered as an array formula in the range A10:A17. The source range A1:A8 in the formula is simply a test column of 8 cells with the entries Banana, Apple, Apple, Banana, Orange, Pineapple, Orange, and Nut. The formula returns a listing of unique fruits to the destination range A10:A17 of Banana, Apple, "", "", Orange, Pineapple, "", Nut. You could make this more efficient by using the array formula =IF(MATCH(A1:A8,A1:A8,0)=ROW(A1:A8)-MIN(ROW(A1:A8))+1,A1:A8,"") As you can see, the formula successfully extracts the unique values of the source range and keeps them properly updated in the destination range as values are modified in the source range. The problem here is that the resulting destination list is interspersed with blank cells. We want to eliminate the blank cells from the destination range and list the unique values sequentially at the top of the range. At some point we my also wish to have the formula sort the resulting unique values before placing them in the destination range of cells. The destination range COULD return as many items as the source range if all items in the source range were distinct. So you'll need to make due with formulas that evaluate to "" or #N/A for duplicate items in the source list, but those values could appear below the distinct values. The most efficient way to load the distinct values into another range would be with formulas like these (which also sort). C1 [array formula]: =INDEX($A$1:$A$8,MATCH(0,COUNTIF($A$1:$A$8,"<"&$A$ 1:$A$8),0)) C2 [array formula]: =IF(COUNTIF($A$1:$A$8,""&C1),INDEX($A$1:$A$8,MATC H(COUNTIF($A$1:$A$8, "<="&C1),COUNTIF($A$1:$A$8,"<"&$A$1:$A$8),0)), "") Fill C2 down into C3:C8. Given your data in A1:A8, this fills C1:C8 with {"Apple";"Banana";"Nut";"Orange";"Pineapple";"";"" ;""}. This technique is often useful in accounting where the user needs to identify all the unique General Ledger Accounts in a very long list of detail transactions which are repeatedly associated with the same set of account numbers. . . . ... In that case, it'd be more more efficient to use an advanced filter, copying only the unique items in the source range to the destination range followed by sorting the filtered, unique results. Or you could use a pivot table to summarize fields by general ledger number if your general ledger data were in a table. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Returning an address from an array | Excel Discussion (Misc queries) | |||
Matching numbers in an Array and returning values for matched numb | Excel Discussion (Misc queries) | |||
Array formula for unique values | Excel Worksheet Functions | |||
Array formula for unique values | Excel Worksheet Functions | |||
Need to be done in 2 hours!!!! VLOOKUP returning #REF for unique I | Excel Worksheet Functions |