Tuesday, January 17, 2012

OpenOffice.org - copy subtotal cells only

Recently a question was raised how to copy the cells showing sub totals rather than copying data cells and subtotals.
I could not find an easy solution. Below I describe a two-step solution which basically consists of
  • Applying a filter to show only the sub total rows
  • Using Copy / Paste special to get a copy of the sub totals (without the formulas)


The data

Assume you have 2 columns of data like this:

The sub totals

Data -> Subtotals... and then ticking X and OK will result in adding extra rows for sub totals.


This was the starting point of the question being asked.

Applying a filter


Data -> Standard Filter and enter
  • 2 filter criteria for X .*Sum and .*Total in order to capture both the Sum and Total rows
  • Tick Regular expression
  • Tick Copy results to... and enter a cell on the sheet (A16 in this example)
    (this is important: don't copy to another sheet since the formula won't work)


Copy the result

The filter resulted in A16:B20. Two things to note:
  • There are no data rows anymore
  • Column B still contains the formulas


Paste special

Now use Paste special to paste the sub totals into a new position. Deselect Formulas in order to copy the data only. Ensure that everything else is ticked, in particular Numbers.


The result

A copy of the sub totals in D16:E20.
Note that column D does not contain formulas.

3 comments:

  1. excellent, thanks your post save me a bounch of time! Regards.

    ReplyDelete
  2. I've been looking for this for years. Thank you!

    ReplyDelete
  3. That's so good - thanks!
    Bonus tip: I use " Sum$" (without the quotes ) in the Find and Replace dialog - because that has a RegEx option as well. Now I've removed the " Sum" text from the end of each row :-)

    ReplyDelete