I know this is easy

teachemhow24 · May 1st, 2002, 10:00 AM

I want to to carry out an or search. there are 3 column headings which all could have the same value in them. eg type of cheese, fav cheese, last cheese bought. I want to see where blue cheese appears in "any" of the 3 columns. If i do a normal filter it will only show the rows where Blue cheese appears in all three columns. Can anybody help?

Thanks in anticipation!

lenze · May 1st, 2002, 10:17 AM

See Help for Advanced Filter

Examples of Advanced Filter Criteria

Brian from Maui · May 1st, 2002, 10:19 AM

How about conditional formatting, where blue cheese is highlighted.

Go to Fomat/Conditional formatting
Formula is =countif($1:$100,"blue cheese")>0
click format set font color and/or cell pattern, click OK OK.
Blue cheese can be replaced by a cell reference.
Click copy, highlight the area and go to Edit/Paste special/formats

[ This Message was edited by: Brian from Maui on 2002-05-01 10:16 ]

Aladin Akyurek · May 1st, 2002, 10:53 AM

Taking up the suggestion by lenze,

suppose we have A4:C8 the following sample:

{"field1","field2","field3";
"brie","xza","blue cheese";
"zsa","kaz","yre";
"danish blue","blue cheese","goudse";
"blue cheese","danone","bread"}

In A2 enter:

=COUNTIF(A5:C5,"blue cheese")>0 [ same formula as Brian uses in cond format ]

1) Activate A5.
2) Activate Data|Filter|Advanced Filter.
3) Check, if so desired, Copy to another location.
4) Enter $A$4:$C$8 for List range.
5) Enter $A$1:$A$2 for Criteria range.
6) Enter, if checked in step 3, $E$4 for Copy to
7) Activate OK.

You can also use a cell ref instead of "blue cheese".

Aladin

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 1st, 2002, 10:00 AM	#1
teachemhow24 New Member Join Date: Apr 2002 Posts: 1	I want to to carry out an or search. there are 3 column headings which all could have the same value in them. eg type of cheese, fav cheese, last cheese bought. I want to see where blue cheese appears in "any" of the 3 columns. If i do a normal filter it will only show the rows where Blue cheese appears in all three columns. Can anybody help? Thanks in anticipation!

May 1st, 2002, 10:17 AM	#2
lenze MrExcel MVP Join Date: Feb 2002 Location: Helena, MT Posts: 13,690	See Help for Advanced Filter Examples of Advanced Filter Criteria

May 1st, 2002, 10:19 AM	#3
Brian from Maui Board Regular Join Date: Feb 2002 Posts: 7,743	How about conditional formatting, where blue cheese is highlighted. Go to Fomat/Conditional formatting Formula is =countif($1:$100,"blue cheese")>0 click format set font color and/or cell pattern, click OK OK. Blue cheese can be replaced by a cell reference. Click copy, highlight the area and go to Edit/Paste special/formats [ This Message was edited by: Brian from Maui on 2002-05-01 10:16 ]

May 1st, 2002, 10:53 AM	#4
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	Taking up the suggestion by lenze, suppose we have A4:C8 the following sample: {"field1","field2","field3"; "brie","xza","blue cheese"; "zsa","kaz","yre"; "danish blue","blue cheese","goudse"; "blue cheese","danone","bread"} In A2 enter: =COUNTIF(A5:C5,"blue cheese")>0 [ same formula as Brian uses in cond format ] 1) Activate A5. 2) Activate Data\|Filter\|Advanced Filter. 3) Check, if so desired, Copy to another location. 4) Enter $A$4:$C$8 for List range. 5) Enter $A$1:$A$2 for Criteria range. 6) Enter, if checked in step 3, $E$4 for Copy to 7) Activate OK. You can also use a cell ref instead of "blue cheese". Aladin