REPORTING unique values

[Blue Max]

Hello Peo,

Your formula
=IF(ISNA(MATCH(0,COUNTIF($C$1:C1,$B$5:$B$15),0)),"",INDEX($B$5:$B$15,MATCH(0,COUNTIF($C$1:C1,$B$5:$B$15),0)))
is a great solution to a task we have in mind, but is there a way to have it
sort the resulting list when working from an unsorted source?

Thanks,
Blue

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

 

[Blue Max]

Hello Peo,

Your links are very good, but isn't there a single formula that can both
sort and extract unique values at the same time? We would like to go from
the original column directly to the sorted unique column without the middle
sorted column of all values?

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

 

[Bernd P]

Hello,

For this I would suggest my UDF lfreq (use only the first output
column = select only one column for the array formula):
http://www.sulprobil.com/html/listfreq.html

Regards,
Bernd

 

[Blue Max]

Thank you, Bernd. Just two questions:

FIRST, your link gives two sample functions. Is the one on the left only
for numbers and the one on the right for text and numbers? What if the list
has blanks?

SECOND, if we define this function and use it on a spreadsheet, will it be
integral to the spreadsheet? In other words, if we copy the spreadsheet to
another location will it still retain all its functionality?

Thanks,
Richard

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

 

[Bernd P]

Hi Richard,

Both functions should work on both strings and numbers. If you copy
the whole file (worksheets and modules included) then the UDF would be
transferred, too.

You have to allow individual macros, though (if the macro cannot be
run this might be caused by your company's security policy on macros).

Regards,
Bernd

 

[Harlan Grove]

. . . isn't there a single formula that can both
sort and extract unique values at the same time? . . .

....

Almost. If the possibly duplicate values were in a range named D, that
range could contain numbers and text, and you want the distinct
numbers to appear before the distinct text in the results, try the
following array formulas.

E1 [1st distinct value]:
=IF(COUNT(D),MIN(D),INDEX(D,MATCH(0,COUNTIF(D,"<"&D))))

E2 [2nd distinct value]:
=IF(SUM(--(D<=E1))<COUNT(D),MIN(IF(D>E1,D)),
INDEX(D,MATCH(IF(COUNT(E1),0,COUNTIF(D,"<="&E1)),
IF(ISTEXT(D),COUNTIF(D,"<"&D)),0)))

Fill E2 down to get the subsequent distinct values.

Note: for large range D, these formula will take a long time to
recalculate - there are pronounced benefits to using an intermediate
range for sorted values.

 

[Blue Max]

Thanks, Harlan. I just keep thinking that there must be some way to
integrate the two formulas. The problem seems to be that Microsoft has not
provided any standard functions for sorting and filtering arrays so they can
continue to be used in the same formula.

Thanks,
Blue

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

. . . isn't there a single formula that can both
sort and extract unique values at the same time? . . .

...

Almost. If the possibly duplicate values were in a range named D, that
range could contain numbers and text, and you want the distinct
numbers to appear before the distinct text in the results, try the
following array formulas.

E1 [1st distinct value]:
=IF(COUNT(D),MIN(D),INDEX(D,MATCH(0,COUNTIF(D,"<"&D))))

E2 [2nd distinct value]:
=IF(SUM(--(D<=E1))<COUNT(D),MIN(IF(D>E1,D)),
INDEX(D,MATCH(IF(COUNT(E1),0,COUNTIF(D,"<="&E1)),
IF(ISTEXT(D),COUNTIF(D,"<"&D)),0)))

Fill E2 down to get the subsequent distinct values.

Note: for large range D, these formula will take a long time to
recalculate - there are pronounced benefits to using an intermediate
range for sorted values.