ANmarS - Search results

Excel/VBA expert since Sep 1997 (21 years 2 months)

We are moving ...

We are moving to another location, better, faster and more resources .... check it out ANmar.Systems

7 Q & A posts tagged with Filter

May 5

How do I generate small groups from a large Excel file?

I have a large Excel file (~22,000 entries). These entries span across multiple columns (e.g. name, address, phone number, etc.). I would like to break this down into groups of 5, 10, or 20.


Use Filter (Auto Filter, or Advanced filter)
If this will not do it for you, then you might need to consider Subtotal

If both didn't do it, a macro will sure do

mail me here some details and will be glad to do it for you


Wednesday 5/5/2010 9:29:47 AM
Apr 2

Can you insert a drop-down or subcategory into an Excel text filter list?

I have a vocab spreadsheet that filters terms by their assigned Category, but there are a great many categories and I'd prefer to make many of them subcategories within the Category filter (essentially, a drop-down within a drop-down, but within a text filter list), rather than create a separate column from which to filter the subcategories, but so far it does not seem possible!


Using AutoFilter, the only approach I see is what you already did, which is adding another column with the subcategories

However, you can still use some trick to make it in one column by having certain mask in the categories name
So if you have Cat1, Cat2, Cat3
And Cat10 is sub of Cat1, Cat20 is sub of Cat2, etc
Then do this in the category column
Cat1 - Cat2
Meaning combining the two categories into one cell, the do sort and you can now easily visually see the category/subcategory in one column

Does that make sense?

let me know if not


Friday 4/2/2010 2:59:51 PM
Apr 26

In Microsoft Excel, how does protecting a worksheet affect the autofilter function?


In the Protect Sheet dialog, you can select what the user can do

You can select any of the checkboxes in the List including
1- Sort a table
2- Filter table (Show/Hide rows)
3- Select cells


Sunday 4/26/2009 4:36:54 PM
Apr 7

Filtering data in Excel 2007 based on formatting?

I have two columns I need to filter. Some have text and some have text with strike-through (a line going through the text). Does anyone know how I can filter based on the strike-through - or if that's not an option can I filter based on anything that is not formatted like the regular text so it will separate the cells with the strike-through? Thank you.


There have been a lot of requests to read format through formulas

Since it is nit supported yet, here is a user definec function to do that

Function IsStrickeThrough(CellAddress As Range) As Boolean
If CellAddress.Rows.Count = 1 And CellAddress.Columns.Count = 1 Then
IsStrickeThrough = CellAddress.Font.Strikethrough
End If
End Function

To use it...
- Press ALT + F11
- Insert > Module
- Paste this
- Now go to any cell and use it like this
=IsStrickeThrough( A1)
Assuming the cell you want to check is A1

Enjoy it


Tuesday 4/7/2009 9:01:21 AM
Apr 6

In Microsoft Excel, the auto filter does not show all my items that are listed.?

There are a lot of drop downs, but it randomly does not have all come up in the list. If I go to them in the spreadsheet, they are in there correctly but do not appear in the filter drop down. Does anyone know why this is? thanks


Autofilter requires no empty spaces between rows
That could be the reason

Make sure that you disable / enable the Auto filter


Monday 4/6/2009 7:58:05 AM
Jan 21

Urgent! MS Excel Macro help needed!?


For worksheet A, I need to loop through the values in column A, and if AX="cow", then I need to copy cells CX, FX and HX into worksheet B.

Any tips or code on how I can accomplish this?

Thank you very much.


Sure, Advanced Filter is your solution
- While you are in Worksheet B, type the name of the AX field in Worksheet A and type "cow" below it
- Go to Data > Filter > Advanced Filter
- Check the option "Copy to another location"
- And enter the three values, knowing that, the Criteria range is your two cells in Worksheet B

Enjoy it, it is a powerful feature

VBAXLMan is back

Wednesday 1/21/2009 3:21:07 PM
Jan 18

Excel - how can I have a cell auto-completed with a description of another cell filter?

I have a spreadsheet with lots of orders on it that I want to filter by name, I have set up the filters fine and that is working well. However, I want another cell in the same sheet to show what I have name I have chosen to filter.
Is this possible? If so, how.

Any help appreciated.


Any help appreciated.


The AutoFilter does not do that
Or you can not do this in AutoFilter
However, you can do this if you are using Advanced filter

Advanced Filter allows you to filter range based on a criteria in another range
Meaning you can see the filter criteria when you filter the table

But, you will need to use the criteria in cells that are not affected by the filter, I mean you will need to shift the table down starting from A10 for example and put your criteria range in A3:C7 for example
This way you can see the filter settings and the filtered range

VBAXLMan is back to feed your Excel needs

Sunday 1/18/2009 7:31:54 AM