OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[Solved] removing zeros from updating columns

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
freppie
Newbie
Newbie


Joined: 22 May 2012
Posts: 2

PostPosted: Tue May 22, 2012 2:09 am    Post subject: [Solved] removing zeros from updating columns Reply with quote

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


Last edited by freppie on Wed May 23, 2012 1:33 am; edited 1 time in total
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Tue May 22, 2012 3:33 am    Post subject: Reply with quote

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


Last edited by ken johnson on Sun Sep 23, 2012 4:05 am; edited 1 time in total
Back to top
View user's profile Send private message
freppie
Newbie
Newbie


Joined: 22 May 2012
Posts: 2

PostPosted: Wed May 23, 2012 1:31 am    Post subject: Reply with quote

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

Thank you very much!
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group