If I have spreadsheet with 2 columns. Each colum containing a list of names. Lest say column A has Bob, Joe, and Steve in that order. And column B has Joe Steve and Bob int hat order.
How would I filter out all the records that have Bob at once.
If I filter column A for Bob I right away lose the next two rows so I can't further filter column B for Bob too.
Basically is there a way to have multiple column filters work as an AND, meaning together with the previous filters.
You're looking for "advanced filter", it's on the data tab to the right of the filter button. You can filter multiple columns using AND or OR.
http://office.microsoft.com/en-us/excel-help/filter-by-using-advanced-criteria-HP005200178.aspxThe video
here gives a good overview, but doesn't have your specific case. You will need to put Bob in A3, B4, C5 ect. The reason is because criteria in the same row are AND, but you want OR, so you need to put then in different rows in the columns you want to filter.
Yip: If you need to keep on changing the names, you can find a blank call and use that to write the name in, and write in A3, B4, C5 etc =that blank cell. (eg. write Bob in K1, and write in A3, B4, C5 etc =K1, now when you want to switch names just change the name in K1. You will have to clear and re-apply the filter, unless you write a macro to do it automatically.)
(Advanced filters do not work in shared workbooks unless you program it in VBA).
Topic Wiki
