Remove [Variable] from a column using an Array?

[levitt]

Hey all,

So, I'll make it snappy:

If I've got the following column:

Column A
A1 - Red
A2 - Blue
A3 - Invalid
A4 - Pink
A5 - Invalid
A6 - Orange

Is there an array which can translate that information into Column B as such:

Column B
A1 - Red
A2 - Blue
A3 - Pink
A4 - Orange
A5 - ""
A6 - ""

Essentially, getting rid of the specified variable ("Invalid" in this case) and pushing all the other variables to the top?

Previously, I was using this formula:

=IFERROR(INDEX('Raw Data'!G:G,SMALL(IF(ISNUMBER(SEARCH("??*",'Raw Data'!G$3:G$20)),ROW('Raw Data'!G$3:G$20)),ROWS($4:14))),"")

Where I had "1" rather than "Invalid" in Column A, but there are some unintended bugs that are making me want to change and use a variable rather than a number.

Any help is appreciated!

[Ron Coderre]

With your sample data in A1:A6
This ARRAY FORMULA, completed by holding down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER)
begins the listing of NON-Invalid items

B1: =IFERROR(INDEX(A:A,SMALL(IF(SEARCH("Invalid",$A$1:$A$10&"Invalid")>1,ROW($A$1:$A$10)),ROWS($1:1))),"")

EDITED TO INCLUDE THIS REGULAR (NON-ARRAY) ALTERNATIVE:

B1: =IFERROR(INDEX(A:A,SMALL(INDEX((SEARCH("Invalid",$A$1:$A$10&"Invalid")=1)*10^10+ROW($A$1:$A$10),0),ROWS($1:1))),"")

Copy B1 and paste into B2 and down as far as you need.

Is that something you can work with?

[levitt]

With your sample data in A1:A6
This ARRAY FORMULA, completed by holding down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER)
begins the listing of NON-Invalid items

B1: =IFERROR(INDEX(A:A,SMALL(IF(SEARCH("Invalid",$A$1:$A$10&"Invalid")>1,ROW($A$1:$A$10)),ROWS($1:1))),"")

EDITED TO INCLUDE THIS REGULAR (NON-ARRAY) ALTERNATIVE:

B1: =IFERROR(INDEX(A:A,SMALL(INDEX((SEARCH("Invalid",$A$1:$A$10&"Invalid")=1)*10^10+ROW($A$1:$A$10),0),ROWS($1:1))),"")

Copy B1 and paste into B2 and down as far as you need.

Is that something you can work with?

That is perfect!! Ah, you're a life saver.

Thanks