Deifine range ignoring duplicates
 

In Define Name in Excel 2007 I want to create a formula for A:A that excludes
duplicates in the range.

How do you create this formula.

Deifine range ignoring duplicates
 

You could use a column of formulas that would extract and gather together the unique values from
column A, and reference those values using a dynamic named range.

HTH,
Bernie
MS Excel MVP


"Gotroots" wrote in message
...
In Define Name in Excel 2007 I want to create a formula for A:A that excludes
duplicates in the range.

How do you create this formula.

Deifine range ignoring duplicates
 

Put this in one column over:
=IF(COUNTIF($A$1:A1,A1)=1,A1,"")

Put this one column over from that; enter with ctrl+shift+enter:
=IF(ROW()-ROW($C$1:$C$20)+1ROWS(B1:B20)-COUNTBLANK(B1:B20),"",INDIRECT(ADDRESS(SMALL((IF(B 1:B20<"",ROW(B1:B20),ROW()+ROWS(B1:B20))),ROW()-ROW($C$1:$C$20)+1),COLUMN(B1:B20),4)))

HTH,
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Bernie Deitrick" wrote:

You could use a column of formulas that would extract and gather together the unique values from
column A, and reference those values using a dynamic named range.

HTH,
Bernie
MS Excel MVP


"Gotroots" wrote in message
...
In Define Name in Excel 2007 I want to create a formula for A:A that excludes
duplicates in the range.

How do you create this formula.




.

Deifine range ignoring duplicates
 

Hi

Tested out your solution and here is how i got on:

=IF(COUNTIF($A$1:A1,A1)=1,A1,"")

this formula works by removing the duplicates

=IF(ROW()-ROW($C$1:$C$20)+1ROWS(B1:B20)-COUNTBLANK(B1:B20),"",INDIRECT(ADDRESS(SMALL((IF(B 1:B20<"",ROW(B1:B20),ROW()+ROWS(B1:B20))),ROW()-ROW($C$1:$C$20)+1),COLUMN(B1:B20),4)))


Some records were not returned with this formula, don't know why.





"ryguy7272" wrote:

Put this in one column over:
=IF(COUNTIF($A$1:A1,A1)=1,A1,"")

Put this one column over from that; enter with ctrl+shift+enter:
=IF(ROW()-ROW($C$1:$C$20)+1ROWS(B1:B20)-COUNTBLANK(B1:B20),"",INDIRECT(ADDRESS(SMALL((IF(B 1:B20<"",ROW(B1:B20),ROW()+ROWS(B1:B20))),ROW()-ROW($C$1:$C$20)+1),COLUMN(B1:B20),4)))

HTH,
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Bernie Deitrick" wrote:

You could use a column of formulas that would extract and gather together the unique values from
column A, and reference those values using a dynamic named range.

HTH,
Bernie
MS Excel MVP


"Gotroots" wrote in message
...
In Define Name in Excel 2007 I want to create a formula for A:A that excludes
duplicates in the range.

How do you create this formula.




.