function to only show unique cells in range (e.g. B2:B20)
 

I'm trying to figure out how I can automatically only show unique values in a
column range using a function. Is there a way to do this?

Thanks...

function to only show unique cells in range (e.g. B2:B20)
 

Hi,

Select the column and use the Data, Filter, Advanced Filter, copy to a new
location, Unique records only.

Or use a formula like
=IF(COUNTIF(A$1:A1,A1)=1,A1,"")

and copy it down. Only the unique entries will be displayed.

If these help, please click the Yes button.
--
Thanks,
Shane Devenshire


"OnTheEdge" wrote:

I'm trying to figure out how I can automatically only show unique values in a
column range using a function. Is there a way to do this?

Thanks...

function to only show unique cells in range (e.g. B2:B20)
 

XL-2007:
=IFERROR(INDEX(rngA,SMALL(IF(MATCH(rngA,rngA,0)=RO W(INDIRECT("1:"&ROWS(rngA))),MATCH(rngA,rngA,0)),R OWS($1:1))),"")

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

Prior to XL-2007:
=IF(ISERR(SMALL(IF(MATCH(rngA,rngA,0)=ROW(INDIRECT ("1:"&ROWS(rngA))),MATCH(rngA,rngA,0)),ROWS($1:1)) ),"",INDEX(rngA,SMALL(IF(MATCH(rngA,rngA,0)=ROW(IN DIRECT("1:"&ROWS(rngA))),MATCH(rngA,rngA,0)),ROWS( $1:1))))

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


"OnTheEdge" wrote:

I'm trying to figure out how I can automatically only show unique values in a
column range using a function. Is there a way to do this?

Thanks...

function to only show unique cells in range (e.g. B2:B20)
 

Hello,

I suggest to use just the first output column of my UDF lfreq:
http://www.sulprobil.com/html/listfreq.html

Regards,
Bernd

function to only show unique cells in range (e.g. B2:B20)
 

This did what I needed it to do when I used it on the same sheet, but
couldn't get it to work right when referencing another sheet. (Basically
putting unique values on another sheet. e.g.
=IF(COUNTIF(Main!M$19,Main!M24)=1,Main!M24,"")

I haven't looked at the countif function, but I'm guessing I need to strip
out one of the 'Main!' references and change it to the local sheet cell
reference

"ShaneDevenshire" wrote:

Hi,

Select the column and use the Data, Filter, Advanced Filter, copy to a new
location, Unique records only.

Or use a formula like
=IF(COUNTIF(A$1:A1,A1)=1,A1,"")

and copy it down. Only the unique entries will be displayed.

If these help, please click the Yes button.
--
Thanks,
Shane Devenshire


"OnTheEdge" wrote:

I'm trying to figure out how I can automatically only show unique values in a
column range using a function. Is there a way to do this?

Thanks...

function to only show unique cells in range (e.g. B2:B20)
 

Hi,

Adjust my formula to meet your needs. However, notice the $ signs. Also,
you don't check one cell against one cell onless you know they are in exactly
the same locations. So your formula would be more like

=IF(COUNTIF(Main!M$1:Main!M1,A1)=1,Main!M1,"")

This assumes your first entry is in M1 it also assumes you are comparing
with cell A1 on the corrent sheet.

You put a comma within the range reference.

--
Thanks,
Shane Devenshire


"OnTheEdge" wrote:

This did what I needed it to do when I used it on the same sheet, but
couldn't get it to work right when referencing another sheet. (Basically
putting unique values on another sheet. e.g.
=IF(COUNTIF(Main!M$19,Main!M24)=1,Main!M24,"")

I haven't looked at the countif function, but I'm guessing I need to strip
out one of the 'Main!' references and change it to the local sheet cell
reference

"ShaneDevenshire" wrote:

Hi,

Select the column and use the Data, Filter, Advanced Filter, copy to a new
location, Unique records only.

Or use a formula like
=IF(COUNTIF(A$1:A1,A1)=1,A1,"")

and copy it down. Only the unique entries will be displayed.

If these help, please click the Yes button.
--
Thanks,
Shane Devenshire


"OnTheEdge" wrote:

I'm trying to figure out how I can automatically only show unique values in a
column range using a function. Is there a way to do this?

Thanks...

function to only show unique cells in range (e.g. B2:B20)
 

Hi,

You can use Data Advanced filters as well. Very well explained in the
Help menu

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"OnTheEdge" wrote in message
...
I'm trying to figure out how I can automatically only show unique values
in a
column range using a function. Is there a way to do this?

Thanks...