Selecting data with conditional formating

[nitika.manhas]

Hello, I applied conditional formatting on column S and Column X. I want to select all the value highlighted as Red in both the columns.

How can I achieve it? For example, if I am applying a filter on column S based on the color red, then I am missingthe value in column X.

Don't know how to apply a filter on both the columns so I don't miss any highlighted data. Attached picture has the data


Thank you,
Nitika Manhas

 

[vletm]

nitika.manhas
Use a help column .. (right side) ... which shows TRUE if both S & X are 'red' otherwise FALSE
Then You can use that column for filtering.

 

[nitika.manhas]

thank you! Do you apply the if formula based on the cell color?

 

[vletm]

nitika.manhas
Those cells are without colors ... conditional formatting gives colors.
Test eg this way...

 

[nitika.manhas]

I am sorry , I think I fail to explain it properly :-(

I will try again... If I am applying filter to column S(based on color) to pick the highlighted cells than I am missing cells x4,x26,x27 and x28
if I apply filter to Column X(based on color) than I miss S13,S15, S17,S18,S21,S22... I don't want to miss any highlighted cells in column S and Colum X..

 

[vletm]

nitika.manhas
If You would modify AND to OR in that formula, then there will come more TRUEs.
... as You've written in Column S and Column X ...

 

[GraH - Guido]

Another way, is repeating the headers and underneath for :

1. AND criteria enter your conditions on same row
2. OR criteria enter on a new row

Then apply advanced filter and select the conditions range, apply filter in range.

 

[Peter Bartholomew]

In future, Office 365 will allow
= FILTER(Table1, (Table1???<0) + (Table1[X]<0) )

I have converted the data range to a table to make addressing data dynamic.
The plus (+) has been used in place of the Boolean operator OR to retain an array of criteria for the FILTER.

The result is a data extract that changes dynamically in response to both changes in the size of the data table and to the criteria.
The key is that the formula is only to be found in cell Extract!B3. The cells used for the rest of the output are, in fact, empty.

Note: There are places ??? where I wanted an S in square brackets but these were treated as controls by the site.
Perhaps there is an escape character that would allow me to use the bracket as a literal?

 

[GraH - Guido]

Peter,

You can use inline code "[LCODE] formula [/LCODE]". ['S'] seems to be strikeout tag (without the ' ')
= FILTER(Table1, (Table1[S]<0) + (Table1[X]<0) )

 

[nitika.manhas]

Another way, is repeating the headers and underneath for :

1. AND criteria enter your conditions on same row
2. OR criteria enter on a new row

Then apply advanced filter and select the conditions range, apply filter in range.

Hello G,

Thank you for the solution, I did not see any formulas in the sheet, could you please elaborate point 1 and 2?

 

[nitika.manhas]

nitika.manhas
If You would modify AND to OR in that formula, then there will come more TRUEs.
... as You've written in Column S and Column X ...

Thank you SO MUCH Mr.Ninja!!! it was very simple solution, thank you again!

 

[GraH - Guido]

Thank you for the solution, I did not see any formulas in the sheet, could you please elaborate point 1 and 2?

There aren't any.

As you can see in the criteria range (here rows 1 -> 3), you have 2 rows with criteria, each row contains only 1 criteria in the example. In this case "OR" is applied as logic by the advanced filter.
If you'd put them on the same line "AND" logic is applied. It would then filter where S and X are "<0".