A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Advanced Filter - Unique Records Only shows 2 of the first value and is correct for the rest



 
 
Thread Tools Display Modes
  #1  
Old August 31st, 2004, 02:09 AM
L Mehl
external usenet poster
 
Posts: n/a
Default 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  
Old August 31st, 2004, 02:51 AM
Bob Umlas, Excel MVP
external usenet poster
 
Posts: n/a
Default

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  
Old August 31st, 2004, 09:17 AM
dcronje
external usenet poster
 
Posts: n/a
Default

If you are only looking for a unique list you would not need the
Criteria bit.


---
Message posted from http://www.ExcelForum.com/

  #4  
Old September 2nd, 2004, 06:07 AM
L Mehl
external usenet poster
 
Posts: n/a
Default

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  
Old September 2nd, 2004, 06:27 AM
L Mehl
external usenet poster
 
Posts: n/a
Default

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:08 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.