Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filter Unique Values
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filter Unique Values
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. -- Hope this helps. Thanks in advance for your feedback. Gary Brown "Steve" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filter Unique Values
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 -- Hope this helps. Thanks in advance for your feedback. Gary Brown "Steve" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filter Unique Values
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" wrote: 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 -- Hope this helps. Thanks in advance for your feedback. Gary Brown "Steve" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filter Unique Values
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. -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "Steve" wrote: 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" wrote: 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 -- Hope this helps. Thanks in advance for your feedback. Gary Brown "Steve" wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filter Unique Values
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 "Gary Brown" wrote: 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. -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "Steve" wrote: 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" wrote: 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 -- Hope this helps. Thanks in advance for your feedback. Gary Brown "Steve" wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filter Unique Values
On Jan 28, 9:38 pm, Steve wrote:
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/uni...ems-excel-help with the attached workbook. Cheers Michael |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filter for unique values in multiple columns | Excel Discussion (Misc queries) | |||
Filter for Unique Values not working | Excel Discussion (Misc queries) | |||
count unique values & filter | Excel Worksheet Functions | |||
unique filter results in some non-unique records. | Excel Discussion (Misc queries) | |||
Unique Value without using FILTER | Excel Worksheet Functions |