Multiple criteria for filters

[LauraP]

Hello forum!
My boss has asked me to create a rather elaborate filter for a very large
spreadsheet. I need to do a couple of things that seem too complex for the
AutoFilter, and the Advanced Filter keeps giving me an error.

In column A, there are values from 1-11, and I need to filter out all but 2,
3, 4, and 6.

In another column, there are values starting with various letters. I need
to filter out all but those that begin with the letters S, L, and C.

In yet another column, I need to show only those rows that have any entries
at all (which I've figured out how to do with AutoFilter).

Any suggestions? Thanks!

 

[Bob Phillips]

In a spare column add the formula

=AND(OR(A2=2,A2=3,A2=4,A2=6),OR(LEFT(B2,1)="S",LEFT(B2,1)="L",LEFT(B2,1)="C"),C2<>"")

and filter that column for TRUE

 

[LauraP]

Thanks! That's a really good start, and I think I see where you're going.
There are over 7,000 rows of data that I need to sort through, though, and
I'm thinking that in that formula, each the A#'s and B#'s need to have the #
of each individual row (if that makes sense). Am I correct in this
assumption? And if so, is there any easier way to enter this formula without
having to change each formula for 7,000 rows?

Thanks again!

 

[Herbert Seidenberg]

Excel 2007
Advanced Filter with multiple AND/OR
No formulas, just fill in criteria.
http://www.mediafire.com/file/h1kmctzjayw/02_02_09.xlsm

 

[Bob Phillips]

Yes, two ways.

You can select all 7,000 rows, with row 2 the first, enter the formula in
the formula bar, and hit Ctrl-Enter. Excel will adjust each automatically.

The other is to enter it into one row, then move the cursor to the bottom
right of the cell until it changes to a black cross, click the mouse left
button, and drag-copy the formula down.