Autocopy the first cell with more than 30 characters after a criteria is met

[Simensg]

Hi.


I want to take my last question a bit further (history from last session):


"Hi. I am using Excel 2010.

I have a workbook with a web query.

Is it possible to autocopy the first cell that has more than 30 characters to a different sheet?

It would help me a lot, because I then could see if there were any changes from the Website.

Example:

"Sheet1"

Range F1:F238.

F1:F20 String < 30 characters. Don't autocopy

F21: => 30 characters. Then copy to "sheet2" A1

Thanks in advance."


You responded:


"Hi ,


Is this what you are looking for ?


=INDEX(List,MIN(IF(LEN(List)>30,(ROW(List)*(LEN(List)>30))-MIN(ROW(List))+1,99999)))


Enter this as an array formula , using CTRL SHIFT ENTER.


List is a named range referring to $F$1:$F$238


Narayan"


Now i need to take it a bit further:


What if I want to autocopy the first cell with more than 30 characters after a criteria is met? In the webquery I am searching for the word "xx hits" After excel has found this text I want to copy the first string with more than 30 characters? Is this possible?


Kind regards

 

[Hui]

Is is exactly "xx hits"

or is it "13 hits", "132 hits" etc

 

[Simensg]

It is random from 10 - 40. It contains the text "hits" everytime I run the web query.

 

[Faseeh]

Hi Simensg,


Assuming your data in F1:F12 like this:

[pre]

12 hits aaaaaaaaaaaaaaaaaa
26 hits aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
33 hits aaaaaaaaaaaaaaaaaaaaaaa
11 hits aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
1254 HITS aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
28 hits aaaaaaaaaaaaa
24 hits aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
26 hits aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
37 hits aaaaaaaaaaaaa
30 hits aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
13 hits aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
39 hits aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

[/pre]
You can try:


=IFERROR(INDEX($F$1:$F$12,SMALL(IF(LEN($F$1:$F$12)>=30,IF(IFERROR(SEARCH("hits",$F$1:$F$12)>=1,FALSE),ROW($F$1:$F$12))),ROW(A1)),0),"")


...Press ctrl+shift+enter and drag down.


Regards,