Returning an array of unique values?

[Blue Max]

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.

 

[Bernd P]

Hello,

You can use my UDF lfreq, for example:
http://www.sulprobil.com/html/listfreq.html

Regards,
Bernd

 

[Alan Beban]

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

 

[Blue Max]

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
******************

 

[Harlan Grove]

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.

 

[Blue Max]

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

**********

 

[Blue Max]

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]

. . . 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,MATCH(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.

 

[Alan Beban]

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

 

[Blue Max]

Alan,

Thank you very much for the suggestions and other resources.

***********************

 

[Blue Max]

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

************************

. . . 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,MATCH(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.