HELP! Need to copy all the names from column A that has COMPLETE status in column B

[ching09]

Hi Please help. Need a formula to have all the Names (Column A) with complete status (column B) on a separate column (in this case, column E) and leave out the Incomplete ones:

--A--------- B--------------- E
Names----Status------------Complete List
Adelle----Complete----------Adelle
John-----Complete----------John
Susan----Incomplete--------George
George---Complete
Anna-----Incomplete

Thanks in advance!

[Sam Capricci]

is something like this what you are referring to? =IF(B2="complete",A2,"")

[ching09]

is something like this what you are referring to? =IF(B2="complete",A2,"")

Thanks Sambo kid! Well, kinda, it's just that I want to cover all of column A and B on the formula and not just B2 to A2, B3 to A3... so that I will have a list without empty spaces in it.

[JohnTopley]

Try

=IFERROR(INDEX($A$2:$A$6,SMALL(IF($B$2:$B$6="Complete",ROW($A$2:$A$6)-ROW($A$2)+1,""),ROWS($A$2:A2))),"")

Enter with Ctrl+Shift+Enter

[Tony Valko]

Here's another one...

Data Range

	A	B	C	D	E
1	Names	Status	------	------	Complete List
2	Adelle	Complete			Adelle
3	John	Complete			John
4	Susan	Incomplete			George
5	George	Complete
6	Anna	Incomplete

This array formula** entered in E2:

=IFERROR(INDEX(A:A,SMALL(IF(B$2:B$6="Complete",ROW(B$2:B$6)),ROWS(E$2:E2))),"")

** 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.

Copy down until you get blanks.

[ching09]

Thanks all!!!!

[Tony Valko]

You're welcome!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.