Advanced filter and a list

[Epinn]

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]

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

(Dropping all the list stuff, too.)

 

[Biff]

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

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

 

[Epinn]

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 list>create 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


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

(Dropping all the list stuff, too.)

 

[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

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

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

 

[SteveW]

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

 

[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

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

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

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]

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

Yes

Biff

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

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

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

 

[Epinn]

Put the worms back in the can.


I can still read it.
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?

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.....

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

Epinn

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

 

[Biff]

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

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

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

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

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]

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.

 

[SteveW]

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

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

I can still read it.

I delete threads after about 2 days

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?


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.

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

No smiley there I see

 

[SteveW]

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.

 

[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

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

Yes

Biff

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

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

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]

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