filter data

[kevin carter]

Hi
This formula searches an entire column returning unique answers

=IF(B11="","",IF(COUNTIF($B$4:B11,B11)>1,"",B11))

what i need to do is limit the range it searches over
ie from row 4 to row 20

thanks in advance

kevin

 

[Chewy667]

I suggest setting up a name range by going to Insert - Name - Define an
then using the name range in your formula.

=IF(B11="","",IF(COUNTIF("named range",B11)>1,"",B11))

See how that works

 

[kevin carter]

I suggest setting up a name range by going to Insert - Name - Define and
then using the name range in your formula.

=IF(B11="","",IF(COUNTIF("named range",B11)>1,"",B11))

See how that works.

i have created a range from B4 to b14, however when i enter the
formula it returns each occurance of the data the data
=IF(B11="","",IF(COUNTIF(myrange,B11)>1,"",B11))
what i want is for the unique occurence to be returned over the range
ie
column b
home
home
dog
after formula
column h
home
dog

 

[Debra Dalgleish]

The following formula will count the values in range B4:20, or to the
current row, whichever is less:

=IF(B11="","",IF(ROW()>20,IF(COUNTIF($B$4:$B$20,B11)>1,"",B11),
IF(COUNTIF($B$4:B11,B11)>1,"",B11)))