Hi,

Today's discovery:-

I have a *column* of first names, some of which are duplicates. I did a sort and then I tried to do advanced filter and I ticked unique records. The info generated was not 100% correct - one name showed up *twice*. In other words, I didn't get a list of *unique* names. But it was interesting that only ONE name (i.e. the first duplicate) didn't work.

I had a feeling that I should create a list but I wanted to see what would happen if I didn't. Anyway, I backtracked and I created a list (without header) and everything worked fine.

Lesson learned: Must always follow the rule to create a list (list create list) before filtering. We can skip sorting but not creating a list. The results will still be okay if data NOT sorted. However, it may speed up processing if sorted??

Please feel free to comment.

Epinn

If you had added a single row with a header, would it work ok?

(Dropping all the list stuff, too.)



Epinn wrote:

Hi,

Today's discovery:-

I have a *column* of first names, some of which are duplicates. I did a sort and then I tried to do advanced filter and I ticked unique records. The info generated was not 100% correct - one name showed up *twice*. In other words, I didn't get a list of *unique* names. But it was interesting that only ONE name (i.e. the first duplicate) didn't work.

I had a feeling that I should create a list but I wanted to see what would happen if I didn't. Anyway, I backtracked and I created a list (without header) and everything worked fine.

Lesson learned: Must always follow the rule to create a list (list create list) before filtering. We can skip sorting but not creating a list. The results will still be okay if data NOT sorted. However, it may speed up processing if sorted??

Please feel free to comment.

Epinn

--

Dave Peterson

Hello,

I may have opened a can of worms. I can really confuse myself and everyone else. In order to truly understand what I am relaying, one must follow my steps to the dot.

May I refer you to my thread on August 18 titled "List for AutoFilter and list for Subtotals have different meanings?"

Based on my findings on August 18, my answer to Dave's question is "yes" off the top of my head. But I have decided to actually do an experiment and I have discovered more issues and my answer is "yes" and "no."

Now, Dave, do you mean I don't do listcreate list, but just insert a row above the names and enter "names" to the cell? In other words, "names" is the column heading.

Case 1 and Case 2 below work fine but not Case 3. Please note my list is NOT sorted this time.

Case 1

If I select the entire range including the column heading "names" and choose data filter advanced filter ...... everything is fine. There is a difference between auto filter and advanced filter in that the latter doesn't give me the down arrow beside the column heading. That's okay. But how does the system know that "names" is the column heading? I say it doesn't. Please read on.

Case 2

If I DON'T select the column heading "names" and just the names AND the first name DOESN'T have a duplicate, then I have a good list too.

Case 3

If I DON'T select the column heading "names" and just the names AND the first name HAS a duplicate, then there is problem.

This experiment tells me advanced filter doesn't really know that the word "names" is a column heading. It just treats it as one of the names. The new list (unique records) will be fine provided the first text in the range selected (be it the column heading or a true name) DOESN'T have a duplicate.

Should I do auto filter first to get the down arrow before I do advanced filter? Is there a bug?

I am not going to spend any more time experimenting say with a sorted list. In the future, this is what I am going to do. Create a list (list create list) and sort it before I do advanced filter.

Comments welcome.

Epinn

"Dave Peterson" wrote in message ...

If you had added a single row with a header, would it work ok?

(Dropping all the list stuff, too.)



Epinn wrote:

Hi,

Today's discovery:-

I have a *column* of first names, some of which are duplicates. I did a sort and then I tried to do advanced filter and I ticked unique records. The info generated was not 100% correct - one name showed up *twice*. In other words, I didn't get a list of *unique* names. But it was interesting that only ONE name (i.e. the first duplicate) didn't work.

I had a feeling that I should create a list but I wanted to see what would happen if I didn't. Anyway, I backtracked and I created a list (without header) and everything worked fine.

Lesson learned: Must always follow the rule to create a list (list create list) before filtering. We can skip sorting but not creating a list. The results will still be okay if data NOT sorted. However, it may speed up processing if sorted??

Please feel free to comment.

Epinn

--

Dave Peterson

Put the worms back in the can.

A thread a month ago - has long gone.

When I create a list of data cells and do Data, Filter, Advanced Filter...
I get an warning message pointing out that I haven't got a label or did I
want to use the first cell as the heading

In fact I can't actually find a selection that doesn't give this message..

Putting an AutoFilter on the first cell, did enable me to avoid the
warning,
but that's obvious as I'd marked the first cell by doing the AutoFilter.

Seems pretty logical and foolproof to me.

Steve



On Tue, 19 Sep 2006 20:57:48 +0100, Epinn
wrote:

Hello,

I may have opened a can of worms. I can really confuse myself and
everyone else. In order to truly understand what I am relaying, one
must follow my steps to the dot.

May I refer you to my thread on August 18 titled "List for AutoFilter
and list for Subtotals have different meanings?"

Based on my findings on August 18, my answer to Dave's question is "yes"
off the top of my head. But I have decided to actually do an experiment
and I have discovered more issues and my answer is "yes" and "no."

Now, Dave, do you mean I don't do listcreate list, but just insert a
row above the names and enter "names" to the cell? In other words,
"names" is the column heading.

Case 1 and Case 2 below work fine but not Case 3. Please note my list
is NOT sorted this time.

Case 1

If I select the entire range including the column heading "names" and
choose data filter advanced filter ...... everything is fine. There
is a difference between auto filter and advanced filter in that the
latter doesn't give me the down arrow beside the column heading. That's
okay. But how does the system know that "names" is the column heading?
I say it doesn't. Please read on.

Case 2

If I DON'T select the column heading "names" and just the names AND the
first name DOESN'T have a duplicate, then I have a good list too.

Case 3

If I DON'T select the column heading "names" and just the names AND the
first name HAS a duplicate, then there is problem.

This experiment tells me advanced filter doesn't really know that the
word "names" is a column heading. It just treats it as one of the
names. The new list (unique records) will be fine provided the first
text in the range selected (be it the column heading or a true name)
DOESN'T have a duplicate.

Should I do auto filter first to get the down arrow before I do advanced
filter? Is there a bug?

I am not going to spend any more time experimenting say with a sorted
list. In the future, this is what I am going to do. Create a list
(list create list) and sort it before I do advanced filter.

Comments welcome.

Epinn

"Dave Peterson" wrote in message
...

If you had added a single row with a header, would it work ok?

(Dropping all the list stuff, too.)



Epinn wrote:

Hi,

Today's discovery:-

I have a *column* of first names, some of which are duplicates. I did
a sort and then I tried to do advanced filter and I ticked unique
records. The info generated was not 100% correct - one name showed up
*twice*. In other words, I didn't get a list of *unique* names. But
it was interesting that only ONE name (i.e. the first duplicate) didn't
work.

I had a feeling that I should create a list but I wanted to see what
would happen if I didn't. Anyway, I backtracked and I created a list
(without header) and everything worked fine.

Lesson learned: Must always follow the rule to create a list (list
create list) before filtering. We can skip sorting but not creating a
list. The results will still be okay if data NOT sorted. However, it
may speed up processing if sorted??

Please feel free to comment.

Epinn

Put the worms back in the can.

Hmmm, I didn't see a smiley nor a <g.

A thread a month ago - has long gone.

I can still read it.

When I create a list of data cells and do Data, Filter, Advanced Filter..
I get an warning message pointing out that I haven't got a label or did I
want to use the first cell as the heading.

Biff, can you help me out here please. The above only happened to me once this afternoon. Subsequently, I had not got that warning message. What is happening here? What will trigger that message?

In fact I can't actually find a selection that doesn't give this message.

Interesting enough, I am experiencing the opposite and cannot trigger the message even though it happened to me once today. Maybe this is proof that you and I are very different from each other..... ;)

Seems pretty logical and foolproof to me.

Talking about "foolproof," a philosopher once said: "The wise has a lot to learn from the fool."

Epinn

"SteveW" wrote in message news:op.tf46uynzevjsnp@enigma03...
Put the worms back in the can.

A thread a month ago - has long gone.

When I create a list of data cells and do Data, Filter, Advanced Filter..
I get an warning message pointing out that I haven't got a label or did I
want to use the first cell as the heading.

In fact I can't actually find a selection that doesn't give this message.

Putting an AutoFilter on the first cell, did enable me to avoid the
warning,
but that's obvious as I'd marked the first cell by doing the AutoFilter.

Seems pretty logical and foolproof to me.

Steve



On Tue, 19 Sep 2006 20:57:48 +0100, Epinn
wrote:

Hello,

I may have opened a can of worms. I can really confuse myself and
everyone else. In order to truly understand what I am relaying, one
must follow my steps to the dot.

May I refer you to my thread on August 18 titled "List for AutoFilter
and list for Subtotals have different meanings?"

Based on my findings on August 18, my answer to Dave's question is "yes"
off the top of my head. But I have decided to actually do an experiment
and I have discovered more issues and my answer is "yes" and "no."

Now, Dave, do you mean I don't do listcreate list, but just insert a
row above the names and enter "names" to the cell? In other words,
"names" is the column heading.

Case 1 and Case 2 below work fine but not Case 3. Please note my list
is NOT sorted this time.

Case 1

If I select the entire range including the column heading "names" and
choose data filter advanced filter ...... everything is fine. There
is a difference between auto filter and advanced filter in that the
latter doesn't give me the down arrow beside the column heading. That's
okay. But how does the system know that "names" is the column heading?
I say it doesn't. Please read on.

Case 2

If I DON'T select the column heading "names" and just the names AND the
first name DOESN'T have a duplicate, then I have a good list too.

Case 3

If I DON'T select the column heading "names" and just the names AND the
first name HAS a duplicate, then there is problem.

This experiment tells me advanced filter doesn't really know that the
word "names" is a column heading. It just treats it as one of the
names. The new list (unique records) will be fine provided the first
text in the range selected (be it the column heading or a true name)
DOESN'T have a duplicate.

Should I do auto filter first to get the down arrow before I do advanced
filter? Is there a bug?

I am not going to spend any more time experimenting say with a sorted
list. In the future, this is what I am going to do. Create a list
(list create list) and sort it before I do advanced filter.

Comments welcome.

Epinn

"Dave Peterson" wrote in message
...

If you had added a single row with a header, would it work ok?

(Dropping all the list stuff, too.)



Epinn wrote:

Hi,

Today's discovery:-

I have a *column* of first names, some of which are duplicates. I did
a sort and then I tried to do advanced filter and I ticked unique
records. The info generated was not 100% correct - one name showed up
*twice*. In other words, I didn't get a list of *unique* names. But
it was interesting that only ONE name (i.e. the first duplicate) didn't
work.

I had a feeling that I should create a list but I wanted to see what
would happen if I didn't. Anyway, I backtracked and I created a list
(without header) and everything worked fine.

Lesson learned: Must always follow the rule to create a list (list
create list) before filtering. We can skip sorting but not creating a
list. The results will still be okay if data NOT sorted. However, it
may speed up processing if sorted??

Please feel free to comment.

Epinn

On Tue, 19 Sep 2006 21:47:36 +0100, Epinn
wrote:

Put the worms back in the can.

Hmmm, I didn't see a smiley nor a <g.

There wasn't one, you made the comment about worms and cans

A thread a month ago - has long gone.
I can still read it.
I delete threads after about 2 days


When I create a list of data cells and do Data, Filter, Advanced
Filter..
I get an warning message pointing out that I haven't got a label or did I
want to use the first cell as the heading.

Biff, can you help me out here please. The above only happened to me
once this afternoon. Subsequently, I had not got that warning message..
What is happening here? What will trigger that message?

In fact I can't actually find a selection that doesn't give this
message.

Interesting enough, I am experiencing the opposite and cannot trigger
the message even though it happened to me once today.
Maybe this is proof that you and I are very different from each
other..... ;)
you have a smiley but just because you can't repeat it doesn't mean were
are different.
Luckily I can assure you we are.

Seems pretty logical and foolproof to me.

Talking about "foolproof," a philosopher once said: "The wise has a lot
to learn from the fool."

No smiley there I see

Epinn

"SteveW" wrote in message
news:op.tf46uynzevjsnp@enigma03...
Put the worms back in the can.

A thread a month ago - has long gone.

When I create a list of data cells and do Data, Filter, Advanced Filter..
I get an warning message pointing out that I haven't got a label or did I
want to use the first cell as the heading.

In fact I can't actually find a selection that doesn't give this message.

Putting an AutoFilter on the first cell, did enable me to avoid the
warning,
but that's obvious as I'd marked the first cell by doing the AutoFilter.

It's not Data|List that made the data|Filter|advanced filter work the way you
want.

It's that you now had a header cell for that column.

Data|List was added in xl2003 (as Biff mentioned). Data|Filter|Advanced filter
has been around a lot longer.



Epinn wrote:

Hello,

I may have opened a can of worms. I can really confuse myself and everyone else. In order to truly understand what I am relaying, one must follow my steps to the dot.

May I refer you to my thread on August 18 titled "List for AutoFilter and list for Subtotals have different meanings?"

Based on my findings on August 18, my answer to Dave's question is "yes" off the top of my head. But I have decided to actually do an experiment and I have discovered more issues and my answer is "yes" and "no."

Now, Dave, do you mean I don't do listcreate list, but just insert a row above the names and enter "names" to the cell? In other words, "names" is the column heading.

Case 1 and Case 2 below work fine but not Case 3. Please note my list is NOT sorted this time.

Case 1

If I select the entire range including the column heading "names" and choose data filter advanced filter ...... everything is fine. There is a difference between auto filter and advanced filter in that the latter doesn't give me the down arrow beside the column heading. That's okay. But how does the system know that "names" is the column heading? I say it doesn't. Please read on.

Case 2

If I DON'T select the column heading "names" and just the names AND the first name DOESN'T have a duplicate, then I have a good list too.

Case 3

If I DON'T select the column heading "names" and just the names AND the first name HAS a duplicate, then there is problem.

This experiment tells me advanced filter doesn't really know that the word "names" is a column heading. It just treats it as one of the names. The new list (unique records) will be fine provided the first text in the range selected (be it the column heading or a true name) DOESN'T have a duplicate.

Should I do auto filter first to get the down arrow before I do advanced filter? Is there a bug?

I am not going to spend any more time experimenting say with a sorted list. In the future, this is what I am going to do. Create a list (list create list) and sort it before I do advanced filter.

Comments welcome.

Epinn

"Dave Peterson" wrote in message ...

If you had added a single row with a header, would it work ok?

(Dropping all the list stuff, too.)

Epinn wrote:

Hi,

Today's discovery:-

I have a *column* of first names, some of which are duplicates. I did a sort and then I tried to do advanced filter and I ticked unique records. The info generated was not 100% correct - one name showed up *twice*. In other words, I didn't get a list of *unique* names. But it was interesting that only ONE name (i.e. the first duplicate) didn't work.

I had a feeling that I should create a list but I wanted to see what would happen if I didn't. Anyway, I backtracked and I created a list (without header) and everything worked fine.

Lesson learned: Must always follow the rule to create a list (list create list) before filtering. We can skip sorting but not creating a list. The results will still be okay if data NOT sorted. However, it may speed up processing if sorted??

Please feel free to comment.

Epinn

--

Dave Peterson

--

Dave Peterson

If you sorted the list and the first and second entries were duplicates,
Excel considered the first entry as the column header when you used the
filter.

Biff

"Epinn" wrote in message
...
Hi,

Today's discovery:-

I have a *column* of first names, some of which are duplicates. I did a
sort and then I tried to do advanced filter and I ticked unique records.
The info generated was not 100% correct - one name showed up *twice*. In
other words, I didn't get a list of *unique* names. But it was interesting
that only ONE name (i.e. the first duplicate) didn't work.

I had a feeling that I should create a list but I wanted to see what would
happen if I didn't. Anyway, I backtracked and I created a list (without
header) and everything worked fine.

Lesson learned: Must always follow the rule to create a list (list create
list) before filtering. We can skip sorting but not creating a list. The
results will still be okay if data NOT sorted. However, it may speed up
processing if sorted??

Please feel free to comment.

Epinn

I think Biff might have saved my day.

Biff, have you read my experiment on an UNSORTED column (no list created) based on Dave's question? Can you confirm that regardless of whether I create a list or not, whether I sort the list or not, the data in the first selected cell will always be treated as a header? So, there is no bug afterall.

This is my impression based on my experiments and Biff's comments.

Thank you for your attention

Epinn

"Biff" wrote in message ...
If you sorted the list and the first and second entries were duplicates,
Excel considered the first entry as the column header when you used the
filter.

Biff

"Epinn" wrote in message
...
Hi,

Today's discovery:-

I have a *column* of first names, some of which are duplicates. I did a
sort and then I tried to do advanced filter and I ticked unique records.
The info generated was not 100% correct - one name showed up *twice*. In
other words, I didn't get a list of *unique* names. But it was interesting
that only ONE name (i.e. the first duplicate) didn't work.

I had a feeling that I should create a list but I wanted to see what would
happen if I didn't. Anyway, I backtracked and I created a list (without
header) and everything worked fine.

Lesson learned: Must always follow the rule to create a list (list create
list) before filtering. We can skip sorting but not creating a list. The
results will still be okay if data NOT sorted. However, it may speed up
processing if sorted??

Please feel free to comment.

Epinn

Correction

I said I would confuse myself and everyone else and I just did. ;)

Sorry, I want to take out "regardless of whether I create a list or not" from my last post. If I create a list, the system will insert "column x" to indicate a heading. Then it won't treat the first real name as a header. Problem solved.

Now, I am beginning to think that I didn't get the true picture on August 18. Biff, if you don't mind, can you comment on that thread please. I need a perfectionist like you to guide me. I can truly appreciate Bob's compliment (with love and grace) of you. ;)

Epinn

"Epinn" wrote in message ...
I think Biff might have saved my day.

Biff, have you read my experiment on an UNSORTED column (no list created) based on Dave's question? Can you confirm that regardless of whether I create a list or not, whether I sort the list or not, the data in the first selected cell will always be treated as a header? So, there is no bug afterall.

This is my impression based on my experiments and Biff's comments.

Thank you for your attention

Epinn

"Biff" wrote in message ...
If you sorted the list and the first and second entries were duplicates,
Excel considered the first entry as the column header when you used the
filter.

Biff

"Epinn" wrote in message
...
Hi,

Today's discovery:-

I have a *column* of first names, some of which are duplicates. I did a
sort and then I tried to do advanced filter and I ticked unique records.
The info generated was not 100% correct - one name showed up *twice*. In
other words, I didn't get a list of *unique* names. But it was interesting
that only ONE name (i.e. the first duplicate) didn't work.

I had a feeling that I should create a list but I wanted to see what would
happen if I didn't. Anyway, I backtracked and I created a list (without
header) and everything worked fine.

Lesson learned: Must always follow the rule to create a list (list create
list) before filtering. We can skip sorting but not creating a list. The
results will still be okay if data NOT sorted. However, it may speed up
processing if sorted??

Please feel free to comment.

Epinn

August 18. Biff, if you don't mind, can you comment on that thread please.

I don't have that message. I keep them for 10 days then get rid of them. So
if anyone responds to one of my posts after 10 days I won't see it.

As for the behavior when using the List option, I use Excel 2002 which
doesn't have the List option. That was introduced in Excel 2003.

Biff

"Epinn" wrote in message
...
Correction

I said I would confuse myself and everyone else and I just did. ;)

Sorry, I want to take out "regardless of whether I create a list or not"
from my last post. If I create a list, the system will insert "column x" to
indicate a heading. Then it won't treat the first real name as a header.
Problem solved.

Now, I am beginning to think that I didn't get the true picture on August
18. Biff, if you don't mind, can you comment on that thread please. I need
a perfectionist like you to guide me. I can truly appreciate Bob's
compliment (with love and grace) of you. ;)

Epinn

"Epinn" wrote in message
...
I think Biff might have saved my day.

Biff, have you read my experiment on an UNSORTED column (no list created)
based on Dave's question? Can you confirm that regardless of whether I
create a list or not, whether I sort the list or not, the data in the first
selected cell will always be treated as a header? So, there is no bug
afterall.

This is my impression based on my experiments and Biff's comments.

Thank you for your attention

Epinn

"Biff" wrote in message
...
If you sorted the list and the first and second entries were duplicates,
Excel considered the first entry as the column header when you used the
filter.

Biff

"Epinn" wrote in message
...
Hi,

Today's discovery:-

I have a *column* of first names, some of which are duplicates. I did a
sort and then I tried to do advanced filter and I ticked unique records.
The info generated was not 100% correct - one name showed up *twice*. In
other words, I didn't get a list of *unique* names. But it was interesting
that only ONE name (i.e. the first duplicate) didn't work.

I had a feeling that I should create a list but I wanted to see what would
happen if I didn't. Anyway, I backtracked and I created a list (without
header) and everything worked fine.

Lesson learned: Must always follow the rule to create a list (list create
list) before filtering. We can skip sorting but not creating a list. The
results will still be okay if data NOT sorted. However, it may speed up
processing if sorted??

Please feel free to comment.

Epinn

On Tue, 19 Sep 2006 22:16:14 +0100, Biff wrote:

August 18. Biff, if you don't mind, can you comment on that thread
please.

I don't have that message. I keep them for 10 days then get rid of them.
So
if anyone responds to one of my posts after 10 days I won't see it.

Glad to see Im not the only one who doesn't keep them.


As for the behavior when using the List option, I use Excel 2002 which
doesn't have the List option. That was introduced in Excel 2003.

Biff

"Epinn" wrote in message
...
Correction

I said I would confuse myself and everyone else and I just did. ;)

Sorry, I want to take out "regardless of whether I create a list or not"
from my last post. If I create a list, the system will insert "column
x" to
indicate a heading. Then it won't treat the first real name as a header.
Problem solved.

Now, I am beginning to think that I didn't get the true picture on August
18. Biff, if you don't mind, can you comment on that thread please. I
need
a perfectionist like you to guide me. I can truly appreciate Bob's
compliment (with love and grace) of you. ;)

Epinn

"Epinn" wrote in message
...
I think Biff might have saved my day.

Biff, have you read my experiment on an UNSORTED column (no list created)
based on Dave's question? Can you confirm that regardless of whether I
create a list or not, whether I sort the list or not, the data in the
first
selected cell will always be treated as a header? So, there is no bug
afterall.

This is my impression based on my experiments and Biff's comments.

Thank you for your attention

Epinn

"Biff" wrote in message
...
If you sorted the list and the first and second entries were duplicates,
Excel considered the first entry as the column header when you used the
filter.

Biff

"Epinn" wrote in message
...
Hi,

Today's discovery:-

I have a *column* of first names, some of which are duplicates. I did a
sort and then I tried to do advanced filter and I ticked unique records.
The info generated was not 100% correct - one name showed up *twice*. In
other words, I didn't get a list of *unique* names. But it was
interesting
that only ONE name (i.e. the first duplicate) didn't work.

I had a feeling that I should create a list but I wanted to see what
would
happen if I didn't. Anyway, I backtracked and I created a list (without
header) and everything worked fine.

Lesson learned: Must always follow the rule to create a list (list
create
list) before filtering. We can skip sorting but not creating a list.
The
results will still be okay if data NOT sorted. However, it may speed up
processing if sorted??

Please feel free to comment.

Epinn








--
Steve (3)

the first selected cell will always be treated as a header?

Yes

Biff

"Epinn" wrote in message
...
I think Biff might have saved my day.

Biff, have you read my experiment on an UNSORTED column (no list created)
based on Dave's question? Can you confirm that regardless of whether I
create a list or not, whether I sort the list or not, the data in the first
selected cell will always be treated as a header? So, there is no bug
afterall.

This is my impression based on my experiments and Biff's comments.

Thank you for your attention

Epinn

"Biff" wrote in message
...
If you sorted the list and the first and second entries were duplicates,
Excel considered the first entry as the column header when you used the
filter.

Biff

"Epinn" wrote in message
...
Hi,

Today's discovery:-

I have a *column* of first names, some of which are duplicates. I did a
sort and then I tried to do advanced filter and I ticked unique records.
The info generated was not 100% correct - one name showed up *twice*. In
other words, I didn't get a list of *unique* names. But it was interesting
that only ONE name (i.e. the first duplicate) didn't work.

I had a feeling that I should create a list but I wanted to see what would
happen if I didn't. Anyway, I backtracked and I created a list (without
header) and everything worked fine.

Lesson learned: Must always follow the rule to create a list (list create
list) before filtering. We can skip sorting but not creating a list. The
results will still be okay if data NOT sorted. However, it may speed up
processing if sorted??

Please feel free to comment.

Epinn

Biff and Dave (in alphabetical order),

Thank you for your precise insight, guidance and more importantly your *SUPPORT*. I think I am quite clear now.

Summary: Excel treats the first entry of the range selected as a column heading. In order for advanced filter to work properly all the time, one must include a column heading which can be done by creating a list or just keying in the heading to a blank cell above the data. Sorting is optional but preferable.

Cheers,

Epinn

"Biff" wrote in message ...
the first selected cell will always be treated as a header?

Yes

Biff

"Epinn" wrote in message
...
I think Biff might have saved my day.

Biff, have you read my experiment on an UNSORTED column (no list created)
based on Dave's question? Can you confirm that regardless of whether I
create a list or not, whether I sort the list or not, the data in the first
selected cell will always be treated as a header? So, there is no bug
afterall.

This is my impression based on my experiments and Biff's comments.

Thank you for your attention

Epinn

"Biff" wrote in message
...
If you sorted the list and the first and second entries were duplicates,
Excel considered the first entry as the column header when you used the
filter.

Biff

"Epinn" wrote in message
...
Hi,

Today's discovery:-

I have a *column* of first names, some of which are duplicates. I did a
sort and then I tried to do advanced filter and I ticked unique records.
The info generated was not 100% correct - one name showed up *twice*. In
other words, I didn't get a list of *unique* names. But it was interesting
that only ONE name (i.e. the first duplicate) didn't work.

I had a feeling that I should create a list but I wanted to see what would
happen if I didn't. Anyway, I backtracked and I created a list (without
header) and everything worked fine.

Lesson learned: Must always follow the rule to create a list (list create
list) before filtering. We can skip sorting but not creating a list. The
results will still be okay if data NOT sorted. However, it may speed up
processing if sorted??

Please feel free to comment.

Epinn

I don't think sorting is preferable. It may even be undesirable.

Epinn wrote:

Biff and Dave (in alphabetical order),

Thank you for your precise insight, guidance and more importantly your *SUPPORT*. I think I am quite clear now.

Summary: Excel treats the first entry of the range selected as a column heading. In order for advanced filter to work properly all the time, one must include a column heading which can be done by creating a list or just keying in the heading to a blank cell above the data. Sorting is optional but preferable.

Cheers,

Epinn

"Biff" wrote in message ...
the first selected cell will always be treated as a header?

Yes

Biff

"Epinn" wrote in message
...
I think Biff might have saved my day.

Biff, have you read my experiment on an UNSORTED column (no list created)
based on Dave's question? Can you confirm that regardless of whether I
create a list or not, whether I sort the list or not, the data in the first
selected cell will always be treated as a header? So, there is no bug
afterall.

This is my impression based on my experiments and Biff's comments.

Thank you for your attention

Epinn

"Biff" wrote in message
...
If you sorted the list and the first and second entries were duplicates,
Excel considered the first entry as the column header when you used the
filter.

Biff

"Epinn" wrote in message
...
Hi,

Today's discovery:-

I have a *column* of first names, some of which are duplicates. I did a
sort and then I tried to do advanced filter and I ticked unique records.
The info generated was not 100% correct - one name showed up *twice*. In
other words, I didn't get a list of *unique* names. But it was interesting
that only ONE name (i.e. the first duplicate) didn't work.

I had a feeling that I should create a list but I wanted to see what would
happen if I didn't. Anyway, I backtracked and I created a list (without
header) and everything worked fine.

Lesson learned: Must always follow the rule to create a list (list create
list) before filtering. We can skip sorting but not creating a list. The
results will still be okay if data NOT sorted. However, it may speed up
processing if sorted??

Please feel free to comment.

Epinn

--

Dave Peterson