 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
Mac Forum / Applications / Excel / July 2005Custom autofilter
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 > 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 >> I'm not very good at Excel.... Excel 2004 11.1.1, Panther 10.3.9 >> [quoted text clipped - 12 lines] > > 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 > 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. 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 |:>) 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 |:>) 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 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 |:>) > 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 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 |:>) On 7/22/05 5:51 PM, in article BF06B885.3BC41%daiyaNOSPAM@mvps.org.INVALID, >> Hi Daiya- >> [quoted text clipped - 27 lines] > > Daiya > 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 On 7/22/05 1:00 PM, in article BF069E84.3BBDE%daiyaNOSPAM@mvps.org.INVALID, > 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.  SignaturePaul 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. Thanks, Paul. Daiya > On 7/22/05 1:00 PM, in article BF069E84.3BBDE%daiyaNOSPAM@mvps.org.INVALID, > [quoted text clipped - 16 lines] > If I've got some details of that wrong, J.E. will be along in a moment to > put it right. Daiya, Just make your criteria: Weekday Mon Wed Fri On 7/22/05 12:46 AM, in article BF05C839.3B9A2%daiyaNOSPAM@mvps.org.INVALID, > I'm not very good at Excel.... Excel 2004 11.1.1, Panther 10.3.9 > [quoted text clipped - 20 lines] > All suggestions and your time appreciated. > DM SignatureBob Greenblatt [MVP], Macintosh bobgreenblattATmsnDOTcom Autofilter only gives me two criteria....and Advanced Filter is a tad incomprehensible....and the "not S/T" approach worked in Excel 2001... > Daiya, > [quoted text clipped - 30 lines] >> All suggestions and your time appreciated. >> DM 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. On 7/22/05 12:03 PM, in article BF0666E8.3BAC5%daiyaNOSPAM@mvps.org.INVALID, > Autofilter only gives me two criteria....and Advanced Filter is a tad > incomprehensible....and the "not S/T" approach worked in Excel 2001... [quoted text clipped - 33 lines] >>> All suggestions and your time appreciated. >>> DM SignatureBob Greenblatt [MVP], Macintosh bobgreenblattATmsnDOTcom |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.