Removing cells with certin value

[robert26]

Hello!

I have a table A1:D10 (all cells are results from IF function).

I need excel to generate table where all cells with value 'FALSE' are removed. It should look like table F1:I2.

I know that one option would be copy - paste values - find 'FALSE' - delete all shift cells up, however I need this process to happen automatically.

Any suggestions?

Screenshot_148.jpg

[63falcondude]

Try this in F1:

=IFERROR(INDEX(A:A,SMALL(IF(A$1:A$100<>FALSE,ROW(A$1:A$100)),ROWS($1:1))),"") Ctrl Shift Enter

Drag the formula through I1 and then down as far as needed.

[robert26]

Try this in F1:

=IFERROR(INDEX(A:A,SMALL(IF(A$1:A$100<>FALSE,ROW(A$1:A$100)),ROWS($1:1))),"") Ctrl Shift Enter

Drag the formula through I1 and then down as far as needed.

looks like its working. thank you.

[63falcondude]

You're welcome. Happy to help.

[robert26]

Since the database is quite large, it would do this function for apx 7000 cells and calculating takes much time and sometimes glitches. Any alternative formulas? As I understand ctrl+shift+enter is the thing that slows it down.

[JeteMc]

Since you are using the 2010 version of Excel and since you have an approximate number of rows for which you need to apply the formula, you might try the following modification of 63's formula that does not require Ctrl+Shift+Enter to confirm:

Please Login or Register  to view this content.

Let us know if you have any questions.

[FDibbins]

You could also do this with a bunch of helper columns (1 for each column of data), that would test for "non-false" and generate a series of numbers, that a regular INDEX() would use