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.

6 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. Big Data and Hadoop is an ecosystem of open source components that fundamentally changes the way enterprises store, process, and analyze data.

    hadoop training in bangalore

    ReplyDelete
  4. Harvard Business Review named data scientist the "sexiest job of the 21st century".This Data Science course will cover the whole data life cycle ranging from Data Acquisition and Data Storage using R-Hadoop concepts, Applying modelling through R programming using Machine learning algorithms and illustrate impeccable Data Visualization by leveraging on 'R' capabilities.With companies across industries striving to bring their research and analysis (R&A) departments up to speed, the demand for qualified data scientists is rising.
    data science training in bangalore

    ReplyDelete
  5. myTectra Amazon Web Services (AWS) certification training helps you to gain real time hands on experience on AWS. myTectra offers AWS training in Bangalore using classroom and AWS Online Training globally. AWS Training at myTectra delivered by the experienced professional who has atleast 4 years of relavent AWS experince and overall 8-15 years of IT experience. myTectra Offers AWS Training since 2013 and retained the positions of Top AWS Training Company in Bangalore and India.

    aws training in bangalore

    ReplyDelete