REPORTING unique values
 

here is my data:
Mary
Sally
Sally
Mary
John
John
David

I used the following to COUNT the unique values:
=SUMPRODUCT((B5:B15<"")/COUNTIF(B5:B15,B5:B15))

What can I do to get a list that REPORTS the unique data - my result should
look like this:
Mary
Sally
John
David

thanks in advance! jane

REPORTING unique values
 

You need to have a heading (like Name), then you can highlight the
data plus header and then click on Data | Filter | Advanced Filter. In
the pop-up you should click on Unique Records Only as well as Copy to
another location - put a suitable cell reference in the box, such as
F1.

When you click OK then you will have your unique list in column F.

Hope this helps.

Pete

On Nov 5, 9:15 pm, jane wrote:
here is my data:
Mary
Sally
Sally
Mary
John
John
David

I used the following to COUNT the unique values:
=SUMPRODUCT((B5:B15<"")/COUNTIF(B5:B15,B5:B15))

What can I do to get a list that REPORTS the unique data - my result should
look like this:
Mary
Sally
John
David

thanks in advance! jane

REPORTING unique values
 

Use this instead

=SUMPRODUCT((B5:B15<"")/COUNTIF(B5:B15,B5:B15&""))


that will not return an error if all cells are empty


You can use advanced filter which has a built in way, select the list, then
select unique records only then copy to another location. Excel 2007 has a
built in method for this called remove duplicates



--


Regards,


Peo Sjoblom


"jane" wrote in message
...
here is my data:
Mary
Sally
Sally
Mary
John
John
David

I used the following to COUNT the unique values:
=SUMPRODUCT((B5:B15<"")/COUNTIF(B5:B15,B5:B15))

What can I do to get a list that REPORTS the unique data - my result
should
look like this:
Mary
Sally
John
David

thanks in advance! jane

REPORTING unique values
 

Peo Sjoblom wrote:
Use this instead

=SUMPRODUCT((B5:B15<"")/COUNTIF(B5:B15,B5:B15&""))


that will not return an error if all cells are empty


You can use advanced filter which has a built in way, select the list, then
select unique records only then copy to another location. Excel 2007 has a
built in method for this called remove duplicates



Can you post the syntax for using the remove duplicates method?

Thanks,
Alan Beban

REPORTING unique values
 

HI Peo and Pete,
Thank you for responding so quickly!

My result using Peo's formula was 6. My formula gave me 6 also but I needed
a list of the actual names without doing the auto-filter as Pete suggested.

thoughts? jane

"Peo Sjoblom" wrote:

Use this instead

=SUMPRODUCT((B5:B15<"")/COUNTIF(B5:B15,B5:B15&""))


that will not return an error if all cells are empty


You can use advanced filter which has a built in way, select the list, then
select unique records only then copy to another location. Excel 2007 has a
built in method for this called remove duplicates



--


Regards,


Peo Sjoblom


"jane" wrote in message
...
here is my data:
Mary
Sally
Sally
Mary
John
John
David

I used the following to COUNT the unique values:
=SUMPRODUCT((B5:B15<"")/COUNTIF(B5:B15,B5:B15))

What can I do to get a list that REPORTS the unique data - my result
should
look like this:
Mary
Sally
John
David

thanks in advance! jane

REPORTING unique values
 

Hi Peo and Pete,
The result with Peo's formula was 6. My formula also returned 6 but I
really need a list with the actual names without using the auto-filter as
Pete suggested...

thoughts? jane



"Peo Sjoblom" wrote:

Use this instead

=SUMPRODUCT((B5:B15<"")/COUNTIF(B5:B15,B5:B15&""))


that will not return an error if all cells are empty


You can use advanced filter which has a built in way, select the list, then
select unique records only then copy to another location. Excel 2007 has a
built in method for this called remove duplicates



--


Regards,


Peo Sjoblom


"jane" wrote in message
...
here is my data:
Mary
Sally
Sally
Mary
John
John
David

I used the following to COUNT the unique values:
=SUMPRODUCT((B5:B15<"")/COUNTIF(B5:B15,B5:B15))

What can I do to get a list that REPORTS the unique data - my result
should
look like this:
Mary
Sally
John
David

thanks in advance! jane

REPORTING unique values
 

I corrected your formula that counts it, I never said it would return unique
values
If you test your formula and remove all values in your range it will return
a DIV error

To use a formula is rather complicated, here's a link that will do that

http://tinyurl.com/2fwou2


you can download a sample from the same page


http://nwexcelsolutions.com/Download..._records. xls


it's definitely easier to use advanced filter


--


Regards,


Peo Sjoblom





"jane" wrote in message
...
HI Peo and Pete,
Thank you for responding so quickly!

My result using Peo's formula was 6. My formula gave me 6 also but I
needed
a list of the actual names without doing the auto-filter as Pete
suggested.

thoughts? jane

"Peo Sjoblom" wrote:

Use this instead

=SUMPRODUCT((B5:B15<"")/COUNTIF(B5:B15,B5:B15&""))


that will not return an error if all cells are empty


You can use advanced filter which has a built in way, select the list,
then
select unique records only then copy to another location. Excel 2007 has
a
built in method for this called remove duplicates



--


Regards,


Peo Sjoblom


"jane" wrote in message
...
here is my data:
Mary
Sally
Sally
Mary
John
John
David

I used the following to COUNT the unique values:
=SUMPRODUCT((B5:B15<"")/COUNTIF(B5:B15,B5:B15))

What can I do to get a list that REPORTS the unique data - my result
should
look like this:
Mary
Sally
John
David

thanks in advance! jane

REPORTING unique values
 

Making a couple of assumptions.

If you're using this formula to count distinct entries:

=SUMPRODUCT((B5:B15<"")/COUNTIF(B5:B15,B5:B15))

Then there are no empty cells within your range. Otherwise, that formula
would return an error.

I'm also assuming all the data is TEXT (like your posted sample).

So, assuming the above formula is entered in cell C5:

Enter this formula in D5:

=B5

Enter this array formula** in D6 and copy down until you get blanks:

=IF(ROWS(D$5:D6)<=C$5,INDEX(B$5:B$15,MATCH(TRUE,CO UNTIF(D$5:D5,B$5:B$15)=0,0)),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"jane" wrote in message
...
here is my data:
Mary
Sally
Sally
Mary
John
John
David

I used the following to COUNT the unique values:
=SUMPRODUCT((B5:B15<"")/COUNTIF(B5:B15,B5:B15))

What can I do to get a list that REPORTS the unique data - my result
should
look like this:
Mary
Sally
John
David

thanks in advance! jane

REPORTING unique values
 

You can't read? I told her to use advanced filter


"You can use advanced filter which has a built in way, select the list, then
select unique records only then copy to another location. Excel 2007 has a
built in method for this called remove duplicates?"


I thought perhaps it would be easier to select a range and apply filter to
get the values but of course there are ways using a 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)))

entered with ctrl + shift & enter

Where $C$1:C1 is the cell above the cell with the formula, adapt accordingly


HTH



--


Regards,


Peo Sjoblom


"Alan Beban" wrote in message
...
Peo Sjoblom wrote:
Use this instead

=SUMPRODUCT((B5:B15<"")/COUNTIF(B5:B15,B5:B15&""))


that will not return an error if all cells are empty


You can use advanced filter which has a built in way, select the list,
then select unique records only then copy to another location. Excel 2007
has a built in method for this called remove duplicates



Can you post the syntax for using the remove duplicates method?

Thanks,
Alan Beban

REPORTING unique values
 

=IF(ISERR(SMALL(IF(MATCH(Name,Name,0)=ROW(INDIRECT ("1:"&ROWS(Name))),MATCH(Name,Name,0)),ROWS($1:1)) ),"",INDEX(Name,SMALL(IF(MATCH(Name,Name,0)=ROW(IN DIRECT("1:"&ROWS(Name))),MATCH(Name,Name,0)),ROWS( $1:1))))

ctrl+shift+enter, not just enter
copy down as far as needed


"jane" wrote:

here is my data:
Mary
Sally
Sally
Mary
John
John
David

I used the following to COUNT the unique values:
=SUMPRODUCT((B5:B15<"")/COUNTIF(B5:B15,B5:B15))

What can I do to get a list that REPORTS the unique data - my result should
look like this:
Mary
Sally
John
David

thanks in advance! jane

REPORTING unique values
 

Peo Sjoblom wrote:
You can't read? I told her to use advanced filter


"You can use advanced filter which has a built in way, select the list, then
select unique records only then copy to another location. Excel 2007 has a
built in method for this called remove duplicates?"


I thought perhaps it would be easier to select a range and apply filter to
get the values but of course there are ways using a 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)))

entered with ctrl + shift & enter

Where $C$1:C1 is the cell above the cell with the formula, adapt accordingly


HTH



Oh, I can read. I'd hazard a guess that I've been getting paid for doing
it for longer than you've been putting meaningful sentences together.

The mistake I made was trying to make sense out of your statment "Excel
2007 has a built in method for this called remove duplicates." I now
take it that you don't really have in mind anything that's called
"remove duplicates" (except, perhaps, by you).

Thanks for your courtesy.

Alan Beban

REPORTING unique values
 

Oh, I can read. I'd hazard a guess that I've been getting paid for doing
it for longer than you've been putting meaningful sentences together.

The mistake I made was trying to make sense out of your statment "Excel
2007 has a built in method for this called remove duplicates." I now take
it that you don't really have in mind anything that's called "remove
duplicates" (except, perhaps, by you).

Thanks for your courtesy.


So what's your contribution to this thread except being a troll?


--

Regards,

Peo Sjoblom

REPORTING unique values
 

Peo Sjoblom wrote:
Oh, I can read. I'd hazard a guess that I've been getting paid for doing
it for longer than you've been putting meaningful sentences together.

The mistake I made was trying to make sense out of your statment "Excel
2007 has a built in method for this called remove duplicates." I now take
it that you don't really have in mind anything that's called "remove
duplicates" (except, perhaps, by you).

Thanks for your courtesy.



So what's your contribution to this thread except being a troll?


I thought from your comment that there was some new feature in Excel
2007, some new "built-in method" that would deal with an issue that has
been around for years and still comes up, and I was trying to get some
clarification of it. Silly me for taking your nonsense talk to be
meaningful.

Alan Beban

REPORTING unique values
 

Now boys, stop sqabbling!!!

Alan, Peo is quite correct when he says that XL2007 has a built in method
called Remove Duplicates. There is no formula as such. The dialogue is very
similar to the start of the Sort dialogue.

If the OP made a copy of here column of data, then selected Data tabData
ToolsRemove Duplicates
the dialogue asks if you want to continue with the selection or expand it.
You then get asked whether the column has headers, and after proceeding the
result is shown, which in the OP's example would say 2 duplicate values
found and removed, 5 unique values remain.

It is a useful new feature in XL2007 which does deal with what you say is an
issue that has been around for years.
--
Regards
Roger Govier



"Alan Beban" wrote in message
...
Peo Sjoblom wrote:
Oh, I can read. I'd hazard a guess that I've been getting paid for doing
it for longer than you've been putting meaningful sentences together.

The mistake I made was trying to make sense out of your statment "Excel
2007 has a built in method for this called remove duplicates." I now take
it that you don't really have in mind anything that's called "remove
duplicates" (except, perhaps, by you).

Thanks for your courtesy.



So what's your contribution to this thread except being a troll?


I thought from your comment that there was some new feature in Excel 2007,
some new "built-in method" that would deal with an issue that has been
around for years and still comes up, and I was trying to get some
clarification of it. Silly me for taking your nonsense talk to be
meaningful.

Alan Beban

REPORTING unique values
 

Hello Jane,

I suggest to use my UDF lfreq (http://www.sulprobil.com/html/
listfreq.html) and to use its first output column only. Keep in mind
that it sorts the output, though...

Regards,
Bernd

REPORTING unique values
 

WOW everyone!
thank you so much for the help today. I corrected my original formula and
am working on applying the other. Will check back in if there are any more
questions.

Very much appreciate everyone's time and thoughts!
take care, jane

"Bernd P" wrote:

Hello Jane,

I suggest to use my UDF lfreq (http://www.sulprobil.com/html/
listfreq.html) and to use its first output column only. Keep in mind
that it sorts the output, though...

Regards,
Bernd

REPORTING unique values
 

Roger Govier wrote:
Now boys, stop sqabbling!!!

Alan, Peo is quite correct when he says that XL2007 has a built in method
called Remove Duplicates. There is no formula as such. The dialogue is very
similar to the start of the Sort dialogue.

If the OP made a copy of here column of data, then selected Data tabData
ToolsRemove Duplicates
the dialogue asks if you want to continue with the selection or expand it.
You then get asked whether the column has headers, and after proceeding the
result is shown, which in the OP's example would say 2 duplicate values
found and removed, 5 unique values remain.

It is a useful new feature in XL2007 which does deal with what you say is an
issue that has been around for years.

Thanks, Roger. That's exactly the sort of clarification I was looking
for when I posted the seemingly, to me, innocuous, non-challenging,
civil question:

"Can you post the syntax for using the remove duplicates method?

Thanks,
Alan Beban"

and got back an off-the-wall snotty "You can't read?" response.

The reference to a "built-in method . . . called remove duplicates"
sounded very much to me (and still does, though I now recognize the
intended meaning) like a reference to a built-in REMOVEDUPLICATES
function, which I thought was an interesting development.

Thanks again for your clarification,

Alan Beban

REPORTING unique values
 

There are MANY ways to do this!

Count Uniques:
=SUMPRODUCT((A1:A1001<"")/(COUNTIF(A1:A1001,A1:A1001&"")))
=SUMPRODUCT((A1:A1001<"")/(COUNTIF(A1:A1001,A1:A1001)+(A1:A1001="")))
=SUMPRODUCT((A1:A1001<"")/COUNTIF(A2:A1001,A2:A1001&"")*(A1:A105<""))
=SUMPRODUCT(--(A1:A1001<""),1/COUNTIF(A1:A1001,A1:A1001&""))
=SUM(IF(FREQUENCY(IF(LEN(A1:A1001)0,MATCH(A1:A100 1,A1:A1001,0),""),IF(LEN(A1:A1001)0,MATCH(A1:A100 1,A2:A1001,0),""))0,1))
=SUM(--(FREQUENCY(IF(A1:A1001<"",MATCH(A1:A1001,A1:A1001 ,0)),ROW(INDIRECT("1:"&ROWS(A1:A1001))))0))
=SUM(IF(A1:A1001<"",1/COUNTIF(A1:A1001,A1:A1001)))
=SUM(IF(FREQUENCY(IF(A1:A1001<"",MATCH("~"&A1:A10 01,A1:A1001&"",0)),ROW(A1:A1001)-ROW(A1)+1),1))
=COUNT(1/FREQUENCY(A1:A1001,A1:A1001))

List Uniques:
Cell C1 will have the result of "Count Uniques" from above:
=IF(ROWS(C$1:C1)<=$C$1,INDEX($A$1:$A$1000,SMALL(IF (FREQUENCY(IF($A$1:$A$1000<"",MATCH("~"&$A$1:$A$1 000,$A$1:$A$1000&"",0)),ROW($A$1:$A$1000)-ROW($A$1)+1),ROW($A$1:$A$1000)-ROW($A$1)+1),ROWS(C$1:C1))),"")
(this does the same thing as TM's Function).


Regards,
Ryan---
--
RyGuy


"Teethless mama" wrote:

=IF(ISERR(SMALL(IF(MATCH(Name,Name,0)=ROW(INDIRECT ("1:"&ROWS(Name))),MATCH(Name,Name,0)),ROWS($1:1)) ),"",INDEX(Name,SMALL(IF(MATCH(Name,Name,0)=ROW(IN DIRECT("1:"&ROWS(Name))),MATCH(Name,Name,0)),ROWS( $1:1))))

ctrl+shift+enter, not just enter
copy down as far as needed


"jane" wrote:

here is my data:
Mary
Sally
Sally
Mary
John
John
David

I used the following to COUNT the unique values:
=SUMPRODUCT((B5:B15<"")/COUNTIF(B5:B15,B5:B15))

What can I do to get a list that REPORTS the unique data - my result should
look like this:
Mary
Sally
John
David

thanks in advance! jane

REPORTING unique values
 

You're very welcome, Alan

--
Regards
Roger Govier



"Alan Beban" wrote in message
...
Roger Govier wrote:
Now boys, stop sqabbling!!!

Alan, Peo is quite correct when he says that XL2007 has a built in method
called Remove Duplicates. There is no formula as such. The dialogue is
very similar to the start of the Sort dialogue.

If the OP made a copy of here column of data, then selected Data tabData
ToolsRemove Duplicates
the dialogue asks if you want to continue with the selection or expand
it.
You then get asked whether the column has headers, and after proceeding
the result is shown, which in the OP's example would say 2 duplicate
values found and removed, 5 unique values remain.

It is a useful new feature in XL2007 which does deal with what you say is
an issue that has been around for years.

Thanks, Roger. That's exactly the sort of clarification I was looking for
when I posted the seemingly, to me, innocuous, non-challenging, civil
question:

"Can you post the syntax for using the remove duplicates method?

Thanks,
Alan Beban"

and got back an off-the-wall snotty "You can't read?" response.

The reference to a "built-in method . . . called remove duplicates"
sounded very much to me (and still does, though I now recognize the
intended meaning) like a reference to a built-in REMOVEDUPLICATES
function, which I thought was an interesting development.

Thanks again for your clarification,

Alan Beban

REPORTING unique values
 

ryguy7272 wrote:
There are MANY ways to do this!

Count Uniques:
=SUMPRODUCT((A1:A1001<"")/(COUNTIF(A1:A1001,A1:A1001&"")))
=SUMPRODUCT((A1:A1001<"")/(COUNTIF(A1:A1001,A1:A1001)+(A1:A1001="")))
=SUMPRODUCT((A1:A1001<"")/COUNTIF(A2:A1001,A2:A1001&"")*(A1:A105<""))
=SUMPRODUCT(--(A1:A1001<""),1/COUNTIF(A1:A1001,A1:A1001&""))
=SUM(IF(FREQUENCY(IF(LEN(A1:A1001)0,MATCH(A1:A100 1,A1:A1001,0),""),IF(LEN(A1:A1001)0,MATCH(A1:A100 1,A2:A1001,0),""))0,1))
=SUM(--(FREQUENCY(IF(A1:A1001<"",MATCH(A1:A1001,A1:A1001 ,0)),ROW(INDIRECT("1:"&ROWS(A1:A1001))))0))
=SUM(IF(A1:A1001<"",1/COUNTIF(A1:A1001,A1:A1001)))
=SUM(IF(FREQUENCY(IF(A1:A1001<"",MATCH("~"&A1:A10 01,A1:A1001&"",0)),ROW(A1:A1001)-ROW(A1)+1),1))
=COUNT(1/FREQUENCY(A1:A1001,A1:A1001))

List Uniques:
Cell C1 will have the result of "Count Uniques" from above:
=IF(ROWS(C$1:C1)<=$C$1,INDEX($A$1:$A$1000,SMALL(IF (FREQUENCY(IF($A$1:$A$1000<"",MATCH("~"&$A$1:$A$1 000,$A$1:$A$1000&"",0)),ROW($A$1:$A$1000)-ROW($A$1)+1),ROW($A$1:$A$1000)-ROW($A$1)+1),ROWS(C$1:C1))),"")
(this does the same thing as TM's Function).


Regards,
Ryan---

And if the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook

=ArrayUniques(A1:A1001), array entered, will list 'em vertically,

and

=ArrayUniques(A1:A1001,,"1horiz"), array entered, will list 'em
horizontally.

Or, if array entering is inconvenient, you can use

=INDEX(ArrayUniques($A$31:$A$1001),ROW(A1),1) filled down, or

=INDEX(ArrayUniques($A$31:$A$1001,,"1horiz"),1,COL UMN(A1)), filled across.

And

=ArrayCount(ArrayUniques(A1:A1001)) will count 'em.

Alan Beban

REPORTING unique values
 

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

***************
"Peo Sjoblom" wrote in message
...
You can't read? I told her to use advanced filter


"You can use advanced filter which has a built in way, select the list,
then
select unique records only then copy to another location. Excel 2007 has a
built in method for this called remove duplicates?"


I thought perhaps it would be easier to select a range and apply filter to
get the values but of course there are ways using a 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)))

entered with ctrl + shift & enter

Where $C$1:C1 is the cell above the cell with the formula, adapt
accordingly


HTH



--


Regards,


Peo Sjoblom


"Alan Beban" wrote in message
...
Peo Sjoblom wrote:
Use this instead

=SUMPRODUCT((B5:B15<"")/COUNTIF(B5:B15,B5:B15&""))


that will not return an error if all cells are empty


You can use advanced filter which has a built in way, select the list,
then select unique records only then copy to another location. Excel
2007 has a built in method for this called remove duplicates



Can you post the syntax for using the remove duplicates method?

Thanks,
Alan Beban

REPORTING unique values
 

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?

***************
"Peo Sjoblom" wrote in message
...
I corrected your formula that counts it, I never said it would return
unique values
If you test your formula and remove all values in your range it will
return a DIV error

To use a formula is rather complicated, here's a link that will do that

http://tinyurl.com/2fwou2


you can download a sample from the same page


http://nwexcelsolutions.com/Download..._records. xls


it's definitely easier to use advanced filter


--


Regards,


Peo Sjoblom





"jane" wrote in message
...
HI Peo and Pete,
Thank you for responding so quickly!

My result using Peo's formula was 6. My formula gave me 6 also but I
needed
a list of the actual names without doing the auto-filter as Pete
suggested.

thoughts? jane

"Peo Sjoblom" wrote:

Use this instead

=SUMPRODUCT((B5:B15<"")/COUNTIF(B5:B15,B5:B15&""))


that will not return an error if all cells are empty


You can use advanced filter which has a built in way, select the list,
then
select unique records only then copy to another location. Excel 2007 has
a
built in method for this called remove duplicates



--


Regards,


Peo Sjoblom


"jane" wrote in message
...
here is my data:
Mary
Sally
Sally
Mary
John
John
David

I used the following to COUNT the unique values:
=SUMPRODUCT((B5:B15<"")/COUNTIF(B5:B15,B5:B15))

What can I do to get a list that REPORTS the unique data - my result
should
look like this:
Mary
Sally
John
David

thanks in advance! jane

REPORTING unique values
 

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

REPORTING unique values
 

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" wrote in message
...
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

REPORTING unique values
 

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

REPORTING unique values
 

"Blue Max" wrote...
....
. . . 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(DE1,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.

REPORTING unique values
 

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

**************
"Harlan Grove" wrote in message
...
"Blue Max" wrote...
...
. . . 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(DE1,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.