Extract cells from a long list based on conditional formatting

[canadian86]

- In column A, I have list of items that take up 700 cells (i.e. the text is in cells A1, A2... A700).

- I have applied conditional formatting to these 700 cells, so some of them are now highlight YELLOW based on a criteria (i.e. cells A3, A14, A422, A654 are yellow)

- I want to create a list of these yellow cells in column B.

Do u know how I can do that?

Thanks a lot.

 

[Haseeb Avarakkan]

Hello;

What formula or condition you are using in A:A to highlight yellow?

 

[canadian86]

If cell contains the word "install", highlight it yellow.

 

[Haseeb Avarakkan]

Try;

Excel 2007 +, B1, Confirmed with CTRL+SHIFT+ENTER, not just ENTER, copy down...

=IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH("install",$A$1:$A$1000)),ROW($A$1:$A$1000)),ROWS(B$1:B1))),"")

Excel 2003 -,

C1; =COUNTIF(A:A,"*install*")

B1, Confirmed with CTRL+SHIFT+ENTER, not just ENTER, copy down...

=IF(ROWS(B$1:B1)<=$C$1,INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH("install",$A$1:$A$1000)),ROW($A$1:$A$1000)),ROWS(B$1:B1))),"")

 

[canadian86]

I have Excel 2007 and that worked -- thanks a lot!!

 

[wulflyng]

I tried this for a similar problem (in Excel 2003) and it works but
I am not sure I entirely understand why.
Won't the "search" always return the same answer - based upon
whether A1 is "install" or not ?

tx