excel 2007 and if then statements

[MegaC]

Hi there,

I am new to this group. Nice to meet everyone!


I wish to write a formula in excel to pull data according to the
following algorithm:

For each filled row in Column I] do
If row[Column I] = "Automation Done" Then
get the contents of row [Column E]
End
End

 

[T. Valko]

Assume you want the results to appear starting in cell A1.

Enter this array formula** in A1 and copy down until you get blanks meaning
all data has been returned:

=IF(ROWS(A$1:A1)<=COUNTIF(I$1:I$20,"Automation
done"),INDEX(E$1:E$20,SMALL(IF(I$1:I$20="Automation
done",ROW(I$3:I$20)),ROWS(A$1:A1))-MIN(ROW(I$3:I$20))+1),"")

You can save a few keystrokes by using a cell to hold "Automation done" and
then reference that cell:

B1 = Automation done

=IF(ROWS(A$1:A1)<=COUNTIF(I$1:I$20,B$1),INDEX(E$1:E$20,SMALL(IF(I$1:I$20=B$1,ROW(I$3:I$20)),ROWS(A$1:A1))-MIN(ROW(I$3:I$20))+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.