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

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

On Jan 23, 4:22*pm, "Meebers" wrote:
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

On Wed, 23 Jan 2008 11:22:05 -0500, "Meebers" wrote:

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

"Ron Rosenfeld" wrote in message
...
On Wed, 23 Jan 2008 11:22:05 -0500, "Meebers" wrote:

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

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" wrote in message
...

"Ron Rosenfeld" wrote in message
...
On Wed, 23 Jan 2008 11:22:05 -0500, "Meebers"
wrote:

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

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

On Wed, 23 Jan 2008 13:47:03 -0500, "Meebers" wrote:


"Ron Rosenfeld" wrote in message
.. .
On Wed, 23 Jan 2008 11:22:05 -0500, "Meebers" wrote:

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

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

On Wed, 23 Jan 2008 14:14:33 -0500, "Meebers" wrote:

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

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" wrote in message
...
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

"Ron Rosenfeld" wrote in message
...
On Wed, 23 Jan 2008 14:14:33 -0500, "Meebers" wrote:

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

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

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

Tyro

"Tyro" wrote in message
. ..
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" wrote in message
...
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

On Wed, 23 Jan 2008 21:09:34 -0500, "Meebers" wrote:


"Ron Rosenfeld" wrote in message
.. .
On Wed, 23 Jan 2008 14:14:33 -0500, "Meebers" wrote:

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

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/ex...Learn%20mor e


--ron