Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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... |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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... |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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... |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count unique values in 1 column where cells in another show non-bl | Excel Discussion (Misc queries) | |||
"COUNTU" function in Excel to count unique entries in a range | Excel Worksheet Functions | |||
Sum of unique values within a range with blank cells | Excel Worksheet Functions | |||
Using a listbox to show every unique record in a range | Excel Worksheet Functions | |||
defining unique range of cells for different sheets as the same n. | Excel Discussion (Misc queries) |