filter data

K

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
 
C

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
 
K

kevin carter

Chewy667 said:
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.
Click to expand...

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
 
D

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)))
 
Top