Custom autofilter

[Daiya Mitchell]

I'm not very good at Excel.... Excel 2004 11.1.1, Panther 10.3.9

I have a list, as below but longer, in two columns for weekday and date. I
managed to get AutoFilter--Custom Filter to show only Mondays and Wednesdays
and the associated dates. Now I am having difficulties setting up a Custom
AutoFilter to show only Mondays, Wednesdays, and Fridays.

Previously (in a different file and I think Excel 2001) I know I
accomplished this by using "does not begin with S or T" but when I try that
now, nothing happens. The list just stays there. I've tried "and" and
"or".

Weekday Date
Mon 26 September
Tue 27 September
Wed 28 September
Thu 29 September
Fri 30 September
Sat 1 October
Sun 2 October
Mon 3 October

All suggestions and your time appreciated.
DM

 

[JE McGimpsey]

I'm not very good at Excel.... Excel 2004 11.1.1, Panther 10.3.9

I have a list, as below but longer, in two columns for weekday and date. I
managed to get AutoFilter--Custom Filter to show only Mondays and Wednesdays
and the associated dates. Now I am having difficulties setting up a Custom
AutoFilter to show only Mondays, Wednesdays, and Fridays.

One way:

Custom Autofilter/Show rows where: Weekday

does not begin with T

and

does not begin with S

 

[Bob Greenblatt]

Daiya,

Just make your criteria:
Weekday
Mon
Wed
Fri

 

[Daiya Mitchell]

I'm not very good at Excel.... Excel 2004 11.1.1, Panther 10.3.9

One way:

Custom Autofilter/Show rows where: Weekday

does not begin with T

and

does not begin with S

Yep. Everytime I try exactly that, nothing happens (sorry, just realized
previous message was elliptical about whether I did it correctly). The same
list just sits there, though the little autofilter arrows turn blue.

I now also just tried "does not contain U" and nothing happened. "Does not
equal" settings for Sat and/or Sun also have no effect. Show rows where
"Does not equal Mon" did nothing.

I don't need to select anything first, do I? I'm supposed to be able to
repeat different autofilters on the same column, right? Do these commands
work on other people's computers?

Daiya

 

[Daiya Mitchell]

Autofilter only gives me two criteria....and Advanced Filter is a tad
incomprehensible....and the "not S/T" approach worked in Excel 2001...

 

[JE McGimpsey]

I don't need to select anything first, do I? I'm supposed to be able to
repeat different autofilters on the same column, right? Do these commands
work on other people's computers?

Well, the autofilter I gave works on my machine.

If your data is continuous and only one cell is selected, the autofilter
range will expand to include all the contiguous data. Otherwise it only
is applied to the selected cells.

If nothing happens, are you selecting only the headers?

Try selecting all the data, or both columns, then apply the autofilter.

 

[Bob Greenblatt]

Just arrange the criteria as I indicated somewhere on your sheet. Then do an
advanced filter. Insure that the proper range is selected for the table and
then select the 4 cells as criteria.

 

[CyberTaz]

Hi Daiya-

JE is right that the criteria:

does not begin with T

and

does not begin with S

*should* work, but I've found the AutoFilter to be a little quirky in
several versions (on both Mac & PC) if you directly apply some of the
last six items in the list of comparison operators. Try this & see if
it helps:

Does not equal t*
And
Does not equal s*

That ought to do it, but if you go back into the Custom dialog box the
criteria will have changed itself.

Regards |:>)

 

[CyberTaz]

Bob's suggestion is even more 'foolproof' & not so complicated once
you've done it. If I may elaborate on his idea:

1) Pick any cell on the sheet (but preferably *not* in the same row as
any of your
data. Otherwise you might not be able to get to that area once the list
is filtered until you turn the filter off.)

2) Type in: Weekday
m*
w*
f*
This establishes your 'Criteria Range'

3) Click somewhere in your data range & go to Data>Filter>Advanced
Filter.

4) Click in the Criteria box, then select the 4 cells used in step (2)

5) Click OK

If the other method did/didn't work, this one should give you the same
desired result. One difference is that you'll have to use
Data>Filter>Show All to expand your list when done.

Regards |:>)

 

[Daiya Mitchell]

Okay, I tried everything suggested re AutoFilter and Advanced Filter
multiple times with various things selected, and nothing worked. But I
semi-sorted it, maybe. I have a theory.

I generated the list in the first place by using Fill Series to create the
list of dates as 9/26/2005 etc, copying that list one column over, and then
used Format>Cells>Custom to show the left column as Mon, etc, and the right
column as 26 September. So when I double-click on Mon, it really says
9/26/05.

Theory, then, that certain filtering approaches can't read the letters,
because they aren't really there, they are just formatting laid on top of a
date?

So Advanced Filter seems to not work at all. Custom AutoFilter worked with
"equals Mon or equals Wed" but I can't get it to work with anything else,
even if it's something where I use the preset menu in the dialog to enter
the Sat in "does not equal Sat". (this seems illogical, but it's what
happens)

When this worked in Excel 2001, I had typed the day names once and pasted
them 10 times, but this time I tried to be smart and searched google groups
for a quicker way. And that's what I get for trying to be clever. (this
problem actually crossed my mind but when "equals Mon or equals Wed" worked
I figured Excel had some way to handle it)

To test, AutoFilter and Advanced Filter worked fine when I typed a quick
list of text day names and actual dates, so I was doing it correctly.

Thanks for everyone's time, mucho apologies if that wasted it, but I learned
a lot, anyhow. Suggestions on a quick way to generate a repetitive list of
day names welcomed. Or can I convert my existing list to plain text
somehow? I'd rather do that, as then I know it's accurately matched the day
name with date.

Daiya

 

[CyberTaz]

Hi Daiya-

I'm certain that your theory re formatting is the key issue. Glad you
got it under control. (Bet it was just a test, huh?)

As far as the Weekday names here are 2 options (which amount to the
same thing):

A) Type the day name or abbreviation into 1st cell then use the Fill
Handle to drag for as many columns or rows as necessary.

B) Select the range of cells & enter the first name or abbr. Then go to
Edit>Fill>Series & pick AutoFill.

Regards |:>)

 

[Daiya Mitchell]

Hi Daiya-

I'm certain that your theory re formatting is the key issue. Glad you
got it under control. (Bet it was just a test, huh?)

Whatcha mean, test? I'm generating dates for a syllabus to paste into Word,
for me. On a side note, I can't believe I couldn't find an existing program
or script to do this for me.

As far as the Weekday names here are 2 options (which amount to the
same thing):

Thank you, both methods very helpful.

A) Type the day name or abbreviation into 1st cell then use the Fill
Handle to drag for as many columns or rows as necessary.

I have the hardest time getting the Fill Handle to show up. Takes practice,
I guess.

B) Select the range of cells & enter the first name or abbr. Then go to
Edit>Fill>Series & pick AutoFill.

AutoFill! Of course. I tried so many variations on Fill Series, Date....

Thank you all very much for the help. My new, properly formatted list is now
AutoFiltering excellently. I'll try to remember how it worked a year from
now, when I will probably have lost my syllabus dates file for the previous
year.

Daiya

 

[CyberTaz]

I meant a test of the responders to see just how sharp they are. I heard
that there are some occasional 'traps' set for us unwary victims!

Glad it all worked out!

Regards |:>)

 

[Paul Berkowitz]

Or can I convert my existing list to plain text
somehow? I'd rather do that, as then I know it's accurately matched the day
name with date.

If you type a single straight quote (apostrophe) before a date or a number
it will format and be treated as text instead. Or you can
Format/Cells.../Number/Text to do a set of selected cells all at once.

Dates are actually numbers beneath the surface. (I think it's done as days,
and time as decimal fraction of days, since or before a certain date: Jan 1,
1904 unless you've unchecked "1904 date system" in Preferences/Calculation,
in which case it's Jan 1 1900 - or the day before that). The way the date
and time is displayed is set in Format/Cells.../Number/Date and /Time, but
it will be interpreted as a date and treated as a number unless you specify
/Text or via 'apostrophe.

If I've got some details of that wrong, J.E. will be along in a moment to
put it right.

--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.

 

[Daiya Mitchell]

I meant a test of the responders to see just how sharp they are. I heard
that there are some occasional 'traps' set for us unwary victims!

Not that I've ever heard.

Daiya

 

[Daiya Mitchell]

Thanks, Paul.
Daiya

If you type a single straight quote (apostrophe) before a date or a number
it will format and be treated as text instead. Or you can
Format/Cells.../Number/Text to do a set of selected cells all at once.

Dates are actually numbers beneath the surface. (I think it's done as days,
and time as decimal fraction of days, since or before a certain date: Jan 1,
1904 unless you've unchecked "1904 date system" in Preferences/Calculation,
in which case it's Jan 1 1900 - or the day before that). The way the date
and time is displayed is set in Format/Cells.../Number/Date and /Time, but
it will be interpreted as a date and treated as a number unless you specify
/Text or via 'apostrophe.

If I've got some details of that wrong, J.E. will be along in a moment to
put it right.