Filter Unique Values

[Steve]

I am filtering a list for unique values using Advanced Filter. Once I filter
the first value shows up in the list twice....
A1 Cat
A2 Cat
A3 Dog
A4 Bird
A5 Snake

If I reference the first occurence of Cat (A1) in a formula in B1 such as
=a1 ="Cat", the formula is changed to EXTRACT = "CAT". What is this and how
do I get rid of it. I dont need 2 occurences of CAT

Thanks

 

[Gary Brown]

The 1st item in the Unique list is considered the heading so it takes 'Cat'
in A1 as the Heading then looks at the rest of the list [A2:A5], sees 'Cat'
in the 'detail' and lists it again. To correct this, either add a heading in
Cell A1 and drop the list down one row or put a blank in Cell A1 and drop the
list down one row.

 

[Gary Brown]

The formula changes to 'EXTRACT' because you have a names range in cell A!
called 'EXTRACT'. That does not have anything to do with the double 'CAT'
issue.
You can get rid of the name by selecting...
Insert > Name > Define
Select 'EXTRACT'
select 'Delete' button then OK

 

[Steve]

Thanks for the reply. I don't understand. I highlight the list and do the
advanced filter copy to a new location. I understand the named range but
from a blank but I do not name the range but it shows up. If I were to
include a column heading then is is included in the list of unique values and
it is then named EXTRACT by Excel not me.

 

[Gary Brown]

Excel creates the named range 'EXTRACT' whenever you use the 'Advanced
Filter' option. Microsoft Excel does not clean itself up afterwards so the
name sticks around. This helps if you're reusing the range but is a pain if
you aren't.

 

[Steve]

OK Thanks! Just seems silly that a list of unique values would have the same
value listed twice. I am creating the list problematically and can exclude
the first instance. I was just trying to understand what excel was doing.
Microsoft wisdom I suppose.

Thanks again

 

[Michael.Tarnowski]

I am filtering a list for unique values using Advanced Filter. Once I filter
the first value shows up in the list twice....
A1 Cat
A2 Cat
A3 Dog
A4 Bird
A5 Snake

If I reference the first occurence of Cat (A1) in a formula in B1 such as
=a1 ="Cat", the formula is changed to EXTRACT = "CAT". What is this and how
do I get rid of it. I dont need 2 occurences of CAT

Thanks

An excellent tipp is
Extracting Unique, Duplicate and Missing Items using Formulas:
http://chandoo.org/wp/2008/11/06/unique-duplicate-missing-items-excel-help
with the attached workbook.
Cheers Michael