0

I am attempting to remove blank cell values which also have a formula, from a column in OpenOffice calc.

Row numbers and corresponding data:

1  A1
2  A4
3  
4  A9
5  
6  A98

Each cell in the data column has a formula, so technically they are not blank, but no data shows in the sheet for certain rows.

fwiw, the formula finds unique values of two other columns and copies these to a third column: =IFERROR(INDEX($A$1:$A$100,AGGREGATE(15,6,1/(1/(ISNA(MATCH($A$1:$A$100,$B$1:$B$100,0))*ROW($A$1:$A$100)*($A$1:$A$100<>""))),ROWS($1:3))),"")

Here's my example sheet.

So far I've tried adding a Standard Filter by selecting the data column then going to Standard Filter, choosing the data column, setting condition to =, value to Not Empty, then selecting the Copy to option and pointing to a cell in an empty column in the sheet. Obviously this doesn't work since my cells have a formula in them.

Is there a similarly easy method to do the same thing with cells containing a formula, but where no data exists in the sheet for the same cell?

1

After turning on AutoFilter, follow step 3 from https://askubuntu.com/questions/943662/libreoffice-calc-delete-empty-rows, which it sounds like you did.

step 3 - filter for empty

Now, only the empty rows are shown. Notice the visible rows are 3 and 5. Select and then delete the rows, for example by pressing Ctrl+-.

step 4

Step 5 is to reset the filter. To do this, I selected column C and went to Data -> Filter -> AutoFilter. When I tried it just now, for some reason I had to do it twice before it worked. Notice the rows are back to normal: 1, 2, 3, 4.

The result:

3 non-empty rows

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.