Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple criteria for filters
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! -- LEP |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple criteria for filters
In a spare column add the formula
=AND(OR(A2=2,A2=3,A2=4,A2=6),OR(LEFT(B2,1)="S",LEF T(B2,1)="L",LEFT(B2,1)="C"),C2<"") and filter that column for TRUE -- __________________________________ HTH Bob "LauraP" wrote in message ... 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! -- LEP |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple criteria for filters
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! -- LEP "Bob Phillips" wrote: In a spare column add the formula =AND(OR(A2=2,A2=3,A2=4,A2=6),OR(LEFT(B2,1)="S",LEF T(B2,1)="L",LEFT(B2,1)="C"),C2<"") and filter that column for TRUE -- __________________________________ HTH Bob "LauraP" wrote in message ... 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! -- LEP |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple criteria for filters
Excel 2007
Advanced Filter with multiple AND/OR No formulas, just fill in criteria. http://www.mediafire.com/file/h1kmctzjayw/02_02_09.xlsm |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple criteria for filters
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. -- __________________________________ HTH Bob "LauraP" wrote in message ... 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! -- LEP "Bob Phillips" wrote: In a spare column add the formula =AND(OR(A2=2,A2=3,A2=4,A2=6),OR(LEFT(B2,1)="S",LEF T(B2,1)="L",LEFT(B2,1)="C"),C2<"") and filter that column for TRUE -- __________________________________ HTH Bob "LauraP" wrote in message ... 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! -- LEP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Clearing Multiple Filters? | Excel Discussion (Misc queries) | |||
match multiple criteria ina range from multiple criteria multiplet | Excel Worksheet Functions | |||
How do I add multiple filters | Excel Discussion (Misc queries) | |||
Multiple Filters in one Sheet | Excel Worksheet Functions | |||
Function that filters a list (Database) for criteria in a range a. | Excel Worksheet Functions |