If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
Advanced Filter - Unique Records Only shows 2 of the first value and is correct for the rest
Hello --
I want a list of unique values in a column (one column in a worksheet) for further processing. I use Advanced Filter - Unique Records Only in a recorded macro and it correctly processes the list, except for showing the first value twice; the remaining unique values are correct. Macro code is: Range("app_category").AdvancedFilter _ Action:=xlFilterCopy, _ CriteriaRange:=Range("criteria"), _ CopyToRange:=Range("extract"), _ Unique:=True Values in the source column "app_category" are sorted. The Criteria range is two stacked cells; the top cell is named "criteria"; the cell below that is blank. The Copy To range is named "extract" and is blank. I did not enter a column name because only one column is being processed. If I enter the column name there, the macro generates the error "The extract range has a missing or illegal field name." Can anyone suggest what is causing the duplicate of the first entry? Thanks for any help. Larry Mehl |
#2
|
|||
|
|||
Excel assumes that the top entry is a header. If it's ALL data, you'd be
better off if you insert a row and enter headers. "L Mehl" wrote: Hello -- I want a list of unique values in a column (one column in a worksheet) for further processing. I use Advanced Filter - Unique Records Only in a recorded macro and it correctly processes the list, except for showing the first value twice; the remaining unique values are correct. Macro code is: Range("app_category").AdvancedFilter _ Action:=xlFilterCopy, _ CriteriaRange:=Range("criteria"), _ CopyToRange:=Range("extract"), _ Unique:=True Values in the source column "app_category" are sorted. The Criteria range is two stacked cells; the top cell is named "criteria"; the cell below that is blank. The Copy To range is named "extract" and is blank. I did not enter a column name because only one column is being processed. If I enter the column name there, the macro generates the error "The extract range has a missing or illegal field name." Can anyone suggest what is causing the duplicate of the first entry? Thanks for any help. Larry Mehl |
#3
|
|||
|
|||
If you are only looking for a unique list you would not need the
Criteria bit. --- Message posted from http://www.ExcelForum.com/ |
#4
|
|||
|
|||
Bob --
Thanks for pointing that out. I added column headers. Problem remains ... the first value still appears twice. dcronje -- Thanks for the "criteria" comment. Larry "L Mehl" wrote in message ... Hello -- I want a list of unique values in a column (one column in a worksheet) for further processing. I use Advanced Filter - Unique Records Only in a recorded macro and it correctly processes the list, except for showing the first value twice; the remaining unique values are correct. Macro code is: Range("app_category").AdvancedFilter _ Action:=xlFilterCopy, _ CriteriaRange:=Range("criteria"), _ CopyToRange:=Range("extract"), _ Unique:=True Values in the source column "app_category" are sorted. The Criteria range is two stacked cells; the top cell is named "criteria"; the cell below that is blank. The Copy To range is named "extract" and is blank. I did not enter a column name because only one column is being processed. If I enter the column name there, the macro generates the error "The extract range has a missing or illegal field name." Can anyone suggest what is causing the duplicate of the first entry? Thanks for any help. Larry Mehl |
#5
|
|||
|
|||
I fixed the duplicate first category problem by giving that column the name
"Category", so that it became the first result, and locating the CopyToRange:=Range so that "Category" appeared in the same row as headers for the statistics I calculate for each category. Thanks again for the responses. Larry Mehl "L Mehl" wrote in message ... Hello -- I want a list of unique values in a column (one column in a worksheet) for further processing. I use Advanced Filter - Unique Records Only in a recorded macro and it correctly processes the list, except for showing the first value twice; the remaining unique values are correct. Macro code is: Range("app_category").AdvancedFilter _ Action:=xlFilterCopy, _ CriteriaRange:=Range("criteria"), _ CopyToRange:=Range("extract"), _ Unique:=True Values in the source column "app_category" are sorted. The Criteria range is two stacked cells; the top cell is named "criteria"; the cell below that is blank. The Copy To range is named "extract" and is blank. I did not enter a column name because only one column is being processed. If I enter the column name there, the macro generates the error "The extract range has a missing or illegal field name." Can anyone suggest what is causing the duplicate of the first entry? Thanks for any help. Larry Mehl |
Thread Tools | |
Display Modes | |
|
|