text: Filter by letter | Windows Secrets Lounge
  Thread: Filter by letter
  2009-06-09, 17:08 #1
  I have a spreadsheet that I leave "auto filtered" in each column. I only have about 4 months of information and already it's about 300 rows. As the year goes on, it may be harder to find information. It is sorted by vendor name and I can scroll down to find what I need but it is taking longer and longer however.
  If I want to look at just the vendors whose name begins with "S" for example, I use the filter and then Custom, begins with.....etc. I do this for each letter I'm interested in.
  Is there a faster way of doing this? I really would like something similar to what you can do in Access where I had a "toolbar" where I just click on the letter and got the results. I really don't want to do a ton of macros but maybe that's the only way?
  2009-06-09, 17:11 #2
  You could create a macro with a toolbar and have it do the filtering. You could add each letter and have the macro set the filter accordingly.
  Without code you could just create a column containing the first letter of each item in the column (hardcoded or with a formula) and then filter on this column instead of the other.
  Another option which does not involve creating buttons for each letter is to use a scrollbar from the forms toolbar. Set the min and max to 1 and 26 respectively. You can set the celllink to something like B1 (you do not need to display you can cover it with the toolbar) and then in another (visible) cell close to the scrollbar (like A1) use a formula =Char(64+B1) to display the letter corresponding to 1-26 (A-Z). You can create a macro to set the filter based on the value in A1 and assign it to the scrollbar. Then when the scrollbar moves you see the letter and you stop and it is filtered.
  Autofilter_scrollbar_example.xls (37.0 KB, 29 views)
  2009-06-09, 17:18 #3
  On the toolbar idea, wouldn't the toolbar have to be visible all the time, even if I'm not on that particular spreadsheet? I probably could hide it and / or apply it every time I need it. Thinking out loud here.
  2009-06-09, 17:23 #4
  I wasn't thinking of the literal XL toolbar, more a series of 26 custom macro buttons at the top of the sheet....
  (Have a look at the PS I added to my previous reply) since you responded before I added it and the example with a scrollbar.
  2009-06-09, 17:44 #5
  Wow! That looks neat! Never knew about the forms toolbar before. See? Learn something new every day! Will have to learn more about it.
  That looks really great and works well too. I think I'll give that a try!
  By the way, how do you move it? or can you?
  2009-06-09, 17:49 #6
  Here is an example with a scrollbar and 26 separate buttons. The Buttons all use the same macro, it reads the text from the button and filters on this. This would allow filtering on longer items. that you use alot.
  I forgot in the original to include the "begins with" and this corrects it in both of routines...
  Autofilter_scrollbar_and_Buttons_example.xls (70.5 KB, 32 views)
  2009-06-09, 17:51 #7
  [quote name='weese237' post='779041' date='09-Jun-2009 18:44']By the way, how do you move it? or can you?[/quote]
  Right click it, and it will be selected. Then you can drag it where you want on the sheet
  Note my later reply where I added another sheet with the 26 buttons and the ability to add Custom buttons.
  2009-06-09, 18:09 #8
  That looks great!!! Boy I've got some learning to do!
  Looks like those buttons are from the forms toolbar as well? Will try to figure it out using your excellent example. I'll probably have more questions.
  2009-06-09, 18:18 #9
  [quote name='weese237' post='779044' date='09-Jun-2009 19:09']Looks like those buttons are from the forms toolbar as well?
  I'll probably have more questions.[/quote]
  Yes they are the "Button" from the Forms toolbar, each set to the same macro. The macro filters on the column with anything that begins with whatever is on the button.