Excel - filter by searching multiple columns

[Pickers]

I want to create a filter (splicer) for an excel spreadsheet that effectively does this...

Only show the rows where the word 'blue' exists in any of 3 columns. Some rows 'blue' will be in column B, other rows blue may appear in column C....

Make sense..??
Help?

 

[5abr3]

Assuming your headers are in row 1 and data starts in row 2, add a New column (call it something like "Any value contains blue") then add the following formula in row 2 and copy it down (on the assumption the cell contents are only the colour blue):
=OR(B2="blue",C2="blue",D2="blue")

That will then return true if the value blue appears in any of said columns. You may need to adjust or add more cells. You can then filter on this formula.

If you need it to be any value containing blue then you would need to use the following:
=OR(isnumber(search("blue",B2)),isnumber(search("blue",C2)),isnumber(search("blue",D2)))

Again you may need to tweak that accordingly and you could use that more complex formula if the cell may only contain "blue" or part of it may be "blue".

For anyone else reading this this will not detect blue backgrounds etc. You need to use macros for that.

 

[Pickers]

I think a variation of this is the key. Only one time I may want to filter by blue and other time filter by red, another by green - a splicer would be nice here

 

[5abr3]

The super lazy option is to change the word blue with the address of a cell outside of the search range, use that target cell and enter your text to compare.

Not used slicers much recently so would have to take a look at that tomorrow when I am near a PC with Excel installed if no one else comes along in the mean time.

Edit: the search string containing cell reference would have to be locked with $ signs for both row and column...

 

[MassiveJim]

I would make a 4th column using the formula A2&B2&C2,
then you can filter column D with words containing red or green or whatever

 

[5abr3]

Having had an admittedly brief play, slicers don't look too useful for what you are after as you are trying to search across multiple potential fields whereas the slicer interface appears just to be a more graphical breakout of the filter selection tables.

I suggest you go with 1 of the 3 formulaic approaches that has been suggested and drive your filter on the formula result.

The concatenated version as suggested by MassiveJim is probably the fastest solution if you have less than 1000 data combinations (filter maximum without having to persuade Excel to expand up to 10000 which seems to be a hard limit).

 

[Pickers]

So my solution was this:-
* Create a list of colours, including "ALL" (I put this in a hidden sheet) for data validation.
* Allocated a cell above the table of data (A1) and made it data validation, where values are from the list on hidden sheet.

*Created a column after the 3 columns of "rank your fav colours"
* In this column entered:

=If(A1="all","FILTER",if(or(column1=A1,column2=A1,column3=A1),"FILTER",""))

* Hid this column (named it "apply filter")
* Added a slicer to "apply filter" column which will of course only have the option "FILTER".


Now I can select the colour I want to filter on the drop down cell A1, then force the update by pressing the slicer button "filter".

To remove filters select "all" from drop down list and apply filter.

Works a treat!!