Filter a range of data with multiple criteria
I have a worksheet with about 4000+ users (rows).
I need to filter out the data containing one of the four codes starting with A,D,L, or R. How can I create a formula that will search for the codes in the column starting with the above letters and return a TRUE if it is there and a FALSE if it is not. There are 8 columns per row. I just need to search in one column for the above text. Thanks! |
Filter a range of data with multiple criteria
=or(a2={"a","d","l","r"})
is one way. NeedExcelHelp07 wrote: I have a worksheet with about 4000+ users (rows). I need to filter out the data containing one of the four codes starting with A,D,L, or R. How can I create a formula that will search for the codes in the column starting with the above letters and return a TRUE if it is there and a FALSE if it is not. There are 8 columns per row. I just need to search in one column for the above text. Thanks! -- Dave Peterson |
Filter a range of data with multiple criteria
ok but what if the codes are written like a7?
How can I write the formula so it searches for the "a" even though the 7 is in the cell as well? "Dave Peterson" wrote: =or(a2={"a","d","l","r"}) is one way. NeedExcelHelp07 wrote: I have a worksheet with about 4000+ users (rows). I need to filter out the data containing one of the four codes starting with A,D,L, or R. How can I create a formula that will search for the codes in the column starting with the above letters and return a TRUE if it is there and a FALSE if it is not. There are 8 columns per row. I just need to search in one column for the above text. Thanks! -- Dave Peterson |
Filter a range of data with multiple criteria
One way, in B2, copied down:
=SUMPRODUCT(--ISNUMBER(SEARCH({"a","d","l","r"},A2)))0 Replace SEARCH with FIND if you need it case sensitive. SEARCH is not case sensitive. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "NeedExcelHelp07" wrote: ok but what if the codes are written like a7? How can I write the formula so it searches for the "a" even though the 7 is in the cell as well? |
Filter a range of data with multiple criteria
=SUM(COUNTIF(A2,{"*a*","*D*","*L*","*R*"}))0
or =SUM(COUNTIF(A3,"*"&{"a","D","L","R"}&"*"))0 This actually will test for 9A7 where A is anywhere in the cell. NeedExcelHelp07 wrote: ok but what if the codes are written like a7? How can I write the formula so it searches for the "a" even though the 7 is in the cell as well? "Dave Peterson" wrote: =or(a2={"a","d","l","r"}) is one way. NeedExcelHelp07 wrote: I have a worksheet with about 4000+ users (rows). I need to filter out the data containing one of the four codes starting with A,D,L, or R. How can I create a formula that will search for the codes in the column starting with the above letters and return a TRUE if it is there and a FALSE if it is not. There are 8 columns per row. I just need to search in one column for the above text. Thanks! -- Dave Peterson -- Dave Peterson |
Filter a range of data with multiple criteria
Thanks Max. It worked!
"Max" wrote: One way, in B2, copied down: =SUMPRODUCT(--ISNUMBER(SEARCH({"a","d","l","r"},A2)))0 Replace SEARCH with FIND if you need it case sensitive. SEARCH is not case sensitive. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "NeedExcelHelp07" wrote: ok but what if the codes are written like a7? How can I write the formula so it searches for the "a" even though the 7 is in the cell as well? |
Filter a range of data with multiple criteria
welcome. do check out Dave's response for alternatives
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "NeedExcelHelp07" wrote in message ... Thanks Max. It worked! |
All times are GMT +1. The time now is 10:43 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com