[SOLVED]Remove empty cells and shift up cells.

fivefootnine · Power User Joined: 09 Apr 2012 Posts: 60

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

Villeroy · Posted: Fri Apr 27, 2012 10:26 pm Post subject:

Data>Filter>StandardFilter...
<column> ... <-not empty->
[More Options]
[x] Copy output to <some cell>
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

fivefootnine · Power User Joined: 09 Apr 2012 Posts: 60

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?

Villeroy · Posted: Fri Apr 27, 2012 11:15 pm Post subject:

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 http://forum.openoffice.org

fivefootnine · Power User Joined: 09 Apr 2012 Posts: 60

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!

Villeroy · Posted: Sat Apr 28, 2012 6:19 am Post subject:

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 http://forum.openoffice.org

fivefootnine · Power User Joined: 09 Apr 2012 Posts: 60

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??

Villeroy · Posted: Sat Apr 28, 2012 10:14 am Post subject:

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 http://forum.openoffice.org

fivefootnine · Power User Joined: 09 Apr 2012 Posts: 60

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! :)