[Solved] removing zeros from updating columns

[freppie]

Dear All,

I have a spreadsheet containing around 40 columns and hundreds of rows that contain an IF-function where the output is either a value or a "0". Now I would like to remove the "0" entries in order to produce columns only containing values without gaps were the "0"s were. I can do it by copy paste my columns (into text format) and afterwards use a filter but I would like to have an automated version since sometimes the "0"s update into becoming a number and by copy paste and using filter it just takes forever to update the final results. Any tips on how to set it up to make it automated?

Thanks!
Freppie

[ken johnson]

Perhaps you could adapt one of the methods shown in the attached doc (Auto Zero Removal.ods)...
http://www.mediafire.com/view/?x3fcfj3fs8ue4x1

Column A has cells with either 0,1,2,3,4 or 5.

Column B has a helper formula that returns current row number when current A cell is not zero.
Column C is a combination of INDEX and SMALL functions that refers to the helper formula and gathers all of the column A non-zero values.

Column E is an array formula that achieves the same result as the column C formula without the need for a helper column.

Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).

[freppie]

Grest - it worked beautifully!

My data points started at row 4 so it took me some time to realize that the index function should still start on row 1 and not 4.....

Thank you very much!