Custom Auto Filter

Topic

New Reply

Using Data/Filter/Auto Filter

When I click the dropdown it offers all of the data in the column as well as “Custom”, which
brings up the Custom Autofilter dialog box.

This only offers 2 text boxes for criteria (and/or).

Is there a way to have more than two criteria?

Thank you !

Michael Abrams

Reply | Quote

Replies

If you need more than AutoFilter offers, you can use Advanced Filter. This lets you set up a range for your selection criteria; this range can be as large as you like (within reasonable limits).

Reply | Quote

An alternate to Hans’ recommendation of adv filter, you could create one (or more depending on complexity) columns that check the criteria (you can use one column with AND or ORs or use multiple ones based on some criterion) then you can use autofilter on these columns to simulate the multiple ones on a particular column. You filter on True or False

This works best if you are doing the same type of filtering all the time since they tend ot be “more unique” and less flexible than the actual autofilter. There are some general utility ones (Matching multiple ORs from a list can be done with checking for MATCH from a prepared list, for example). Find/Search can be done for “contains”, etc

It all depends on your needs…

Steve

Reply | Quote

Thanks guys – I will try both methods.

The actual scenario is that I have spreadsheet with 50,000 records.

Column B is filled in by a rep with 1 of 7 different choices.

I am able to use the Autofilter on Column B to filter: “equals” choice 1 or equals choice 2 (for example)

I would like to able to filter: equals choice 1 or equals choice 2 or equals choice 3.

The Autofilter only allows two “ors”, not three.

Michael

Reply | Quote

As mentioned above, you could add a calculated column, with a formula such as

=OR(B2=”Choice1″,B2=”Choice2″,B2=”Choice3″)

in row 2, and filled down as far as needed. The column will display TRUE/FALSE values.

Add an appropriate column header, then turn AutoFilter off and on again so that the new column is included.
Select TRUE from the AutoFilter dropdown for this column.

Or, with 50,000 records – use Access instead of Excel!

Reply | Quote

I couldn’t get the right syntax but what you offered worked fine !
=OR(B2=”Choice1″,B2=”Choice2″,B2=”Choice3″)

Thank you Hans !

Michael

Reply | Quote

Another option I alluded to, especially with a large or variable list, is to put the choices in a range (eg $F$1:$F$10, could even be on a different sheet if desired)

And then use
=ISNUMBER(MATCH(B2,$F$1:$F$10,0))

and copy it down the column.

You can control which are chosen by adding or removing from the range in F1:F10 (the list can contain blanks) without needing to change the formulas being filtered…

Steve

Reply | Quote

Hi Steve – That one is pretty cool too. I’ll give it a shot.

Thank you very much !!

Michael

Reply | Quote