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]Remove empty cells and shift up cells.

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


Joined: 09 Apr 2012
Posts: 60

PostPosted: Fri Apr 27, 2012 9:56 pm    Post subject: [SOLVED]Remove empty cells and shift up cells. Reply with quote

Hi, I need a formula for removing all the blank cells in a row by shifting UP the cells., whilst not affecting the row numbers??

This is what my row looks like

34
blank
blank
36
blank
12
blank
blank
blank
56
67
blank
blank


I´d like it to be
34
36
12
56
67


Last edited by fivefootnine on Sun Apr 29, 2012 1:11 pm; edited 2 times in total
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Apr 27, 2012 10:26 pm    Post subject: Reply with quote

Data>Filter>StandardFilter...
<column> ... <-not empty->
[More Options]
[x] Copy output to <some cell>
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
fivefootnine
Power User
Power User


Joined: 09 Apr 2012
Posts: 60

PostPosted: Fri Apr 27, 2012 10:39 pm    Post subject: Reply with quote

Thanks for the fast answer, I forgot to mention that the empty cells is in fact not empty, they contains formula and therefore not considered as empty by the filter; is there a way to get around this?
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Apr 27, 2012 11:15 pm    Post subject: Reply with quote

Data>Filter>StandardFilter...
<column> ... <the blank entry at the end of the list>
[More Options]
[x] Copy output to <some cell>

#### alternatively ######
create a calculated column with =IF(X1="" ; "xxx" ; X1)
and filter that one that do not equal "xxx"

Calculated helper columns can combine arbitrary complex criteria.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
fivefootnine
Power User
Power User


Joined: 09 Apr 2012
Posts: 60

PostPosted: Sat Apr 28, 2012 5:06 am    Post subject: Reply with quote

Thanks again for your answer, I tried the last option you suggested as I don´t know how to copy output to some cell? (your 2nd option), and it worked great, but the filter messes up the row order.
Id´like a solution that does exactly that, but keeps the row numbers as they are, and instead shift up the data,.. like this (row numbers to the left)

row nr to left

1] xxx
2] 3
3] xxx
4] xxx
5] xxx
6] 4
7] xxx

and I´d like it to be like this....

1] 3
2] 4


Thank you and have a great day!
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat Apr 28, 2012 6:19 am    Post subject: Reply with quote

Sorry, I forgot that the copying filter copies the formulas with their relative references.
Filter in place without copy option.
Then copy from the filtered range and paste-special values without formulas or copy&paste the constant values from the list.
Hidden cells will be skipped.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
fivefootnine
Power User
Power User


Joined: 09 Apr 2012
Posts: 60

PostPosted: Sat Apr 28, 2012 9:23 am    Post subject: Reply with quote

Hi, Sorry, I might be retarded and could need a step by step guide on how to do what you just described. :)

#### alternatively ######
create a calculated column with =IF(X1="" ; "xxx" ; X1)
and filter that one that do not equal "xxx"

This method is the one only one I´ve suceeded with, but I want it to work without affecting the rows, so maybe there´s a another formula that can be used as the final piece in the chain, in order to delete the "xxx" cells without affecting the rows.???sadly the filter seem to do that by standard??
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat Apr 28, 2012 10:14 am    Post subject: Reply with quote

I give up. If all this is not good enough you may use a database where all this is no problem. Even if you keep the empty values in the table you can simply tell the program to ignore them.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
fivefootnine
Power User
Power User


Joined: 09 Apr 2012
Posts: 60

PostPosted: Sun Apr 29, 2012 1:10 pm    Post subject: Reply with quote

I got it now!! thanks, the problem was that you were always two steps ahead of me, didnt know about the paste special feauture,, problem now solved, thanks! :)
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