Bug or wrong approach

[Meebers]

Excel 07 doing advanced filtering. I have a column of numbers, some are
repeated, and I want to filter out unique numbers only. So as a test,
starting in A1 and going down, I input 1,2,3,1,2,3. Under advanced
filtering, I selected the range, a1:a6, want it copied to another location ,
B1, checked unique records only and expect the result to = 1,2,3. The
result is 1,2,3,1. It seems like if the top number (a1) is repeated
anywhere else in the column it will repeat it as a unique number. If I
changed the data to say 1,2,2,3,3,2 the expected result is 1,2,3 and it is.
Bug or approach or ?? TIA

 

[Pete_UK]

You need to have a header for your column of data, otherwise Excel
takes the first value as the header (and hence it repeats). This
applies to earlier versions of Excel also.

Hope this helps.

Pete

 

[Ron Rosenfeld]

Excel 07 doing advanced filtering. I have a column of numbers, some are
repeated, and I want to filter out unique numbers only. So as a test,
starting in A1 and going down, I input 1,2,3,1,2,3. Under advanced
filtering, I selected the range, a1:a6, want it copied to another location ,
B1, checked unique records only and expect the result to = 1,2,3. The
result is 1,2,3,1. It seems like if the top number (a1) is repeated
anywhere else in the column it will repeat it as a unique number. If I
changed the data to say 1,2,2,3,3,2 the expected result is 1,2,3 and it is.
Bug or approach or ?? TIA

I'm not sure about Excel 07, but perhaps the first line is being interpreted as
a label, and not as part of the data.

In 03, the advanced filter depends that the first row be a row of labels.
--ron

 

[Meebers]

I'm not sure about Excel 07, but perhaps the first line is being
interpreted as
a label, and not as part of the data.

In 03, the advanced filter depends that the first row be a row of labels.
--ron

I had tested this as well. First in a1 = "Label" a2:a7 = 1,2,3,1,2,3.
When I selected a2:a7 to filter, I got the same answer 1,2,3,1. When I
changed the List range to = a1:a7, I get the error msg "The extract range
has a missing or illegal field name"

 

[Meebers]

I had tested this as well. First in a1 = "Label" a2:a7 = 1,2,3,1,2,3.
When I selected a2:a7 to filter, I got the same answer 1,2,3,1. When I
changed the List range to = a1:a7, I get the error msg "The extract range
has a missing or illegal field name"

Perhaps a fix.....you have to select a criteria range (even though it might
be blank/empty) then the advance filter works as expected. So.....not a bug
but me :0( Mike

 

[Ron Rosenfeld]

I had tested this as well. First in a1 = "Label" a2:a7 = 1,2,3,1,2,3.
When I selected a2:a7 to filter, I got the same answer 1,2,3,1. When I
changed the List range to = a1:a7, I get the error msg "The extract range
has a missing or illegal field name"

In XL2003, you will get that message if there is no label, or if the label that
is present is not allowable to be used as a label (e.g. if the label were
something like F1).

The criteria range can be left blank
--ron

 

[Ron Rosenfeld]

Perhaps a fix.....you have to select a criteria range (even though it might
be blank/empty) then the advance filter works as expected. So.....not a bug
but me :0( Mike

That requirement is not documented in the Online HELP available for XL2007
--ron

 

[Tyro]

Excel 2007

In A1:A7 I put: Label, 1, 2, 3, 1, 2, 3 Then under advanced filter, I
selected A1:A7, copy to another location, selected B1 and checked unique
records only. In B1 through B4 I got: Label, 1, 2, 3 and that's all.

Tyro

 

[Meebers]

That requirement is not documented in the Online HELP available for XL2007
--ron

I will repeat the test again tomorrow with a fresh mind. I did read the
HELP file, it said to leave at least 3 blank cells above the label field and
in the examples showed that was the place to put the criteria in. I tried
it with and without blanks and the only thing that worked was to choose a
criteria range. I will give it a go on my laptop this time and see if the
results differ. Tx for the input. mike

 

[Tyro]

I should have pointed out, I have SP1 installed too.

Tyro

 

[Ron Rosenfeld]

I will repeat the test again tomorrow with a fresh mind. I did read the
HELP file, it said to leave at least 3 blank cells above the label field and
in the examples showed that was the place to put the criteria in. I tried
it with and without blanks and the only thing that worked was to choose a
criteria range. I will give it a go on my laptop this time and see if the
results differ. Tx for the input. mike

See:

http://office.microsoft.com/en-us/excel/HP100739431033.aspx?pid=CH100648451033#Learn more


--ron