Search code examples
macrosformulalibreofficecalc

Create a new sheet by filtering and reordering existing sheet in LibreOffice Calc

I am using LibreOffice Calc and I have some data that looks like this:

enter image description here

I would like to rearrange the data on a new sheet so that it looks like this:

enter image description here

There is a lot of data (many years and many countries) so I hope there is a way to automate the translation.

Solution

  • JohnSUN is correct that you can achieve what you want using Pivot Table function. Here are the steps on how you can do this:

    1. Select your entire sheet or range of your data.
    2. Data --> Pivot Table --> Insert or Edit.
    3. In the Column Fields, remove "Data"
    4. In the Column Fields, add "Year"
    5. In the Row Fields, add "Country Rank"
    6. In the Data Fields, add "Income". It will show up as "Sum - Income", but this is OK because you only have 1 row of data per year per rank.
    7. Expand Options, and deselect "Total Columns" and "Total Rows"

    See the final Pivot Table Layout screen example

    By default it should create the table in a new sheet.

    If for some reason pivot table is showing blank data, save the file (as ODS), close it, then open and try again.