TOP

Reorganization of tables (Unpivot) in LibreOffice Calc

Description

Sometimes there is a need to convert a cross (pivot) table into a flat one. This procedure is called " data straightening " (unpivot data) . As a result of such " straightening " we get a table where all similar data are in one column.

The program LibreOffice Calc does not have a standard functionality for converting pivot tables to " flat ", but you can write the corresponding procedure yourself. Let's see how to do it.



Let's look at the picture below to understand the essence of the problem more clearly. On the left we have a table in the form of a matrix (pivot table), and on the right - a regular table with columns (flat table):

When you expand a table, you unpack the attribute-value pair that is the intersection of the columns in the matrix table and reorient them into the flattened columns in the flat table:

  • Value (Values) (blue on the left) expand into a new column (blue on the right);
  • Attributes (Attributes) (green on the left) are also expanded into a new column (green on the right) and duplicated according to the new column of values.
  • StarBASIC code for the UnPivotTable procedure

    Open the menu Tools - Macros - Edit Macros..., select Module1 and copy the following text into the module:

    Sub UnPivotTable
      ' moonexcel.com.ua 
      Dim oBook        As Object
      Dim oActiveSheet As Object
      Dim oSelRange    As Object
      Dim oNewSheet    As Object
      
      Dim i                  As Long
      Dim iTopLabelRowCount  As Integer
      Dim iSideLabelColCount As Integer       
      
      oBook        = ThisComponent
      oActiveSheet = oBook.CurrentController.ActiveSheet
      oSelRange    = oBook.CurrentSelection
      
      ' Add a new sheet 
      oBook.Sheets.insertNewByName("UnPivoted Table",0)
      oNewSheet = oBook.Sheets(0)
      
      ' Determine the size of the headers 
      iTopLabelRowCount  = InputBox(" How many lines in the top header? ")
      iSideLabelColCount = InputBox(" How many columns in the side header? ")
                
      i = 0
         
      For r = (iTopLabelRowCount + 1) To oSelRange.Rows.Count
        For c = (iSideLabelColCount + 1) To oSelRange.Columns.Count            
                           
          For j = 1 To iSideLabelColCount              
            oNewSheet.getCellByPosition(j-1, i).Formula = oSelRange.getCellByPosition(j-1, r-1).Formula               
          Next j
                 
          For k = 1 To iTopLabelRowCount
            oNewSheet.getCellByPosition(j + k - 2, i).Formula = oSelRange.getCellByPosition(c-1, k-1).Formula
          Next k           
                             
          oNewSheet.getCellByPosition(j + k - 2, i).Formula = oSelRange.getCellByPosition(c-1, r-1).Formula
          i = i + 1                   
                
        Next c
      Next r
    End Sub
    

    Then, close Macro Editor and return to your worksheet in LibreOffice Calc. Select the entire table with the top and left headers and run our new macro via the menu Tools - Macros - Run Macro...

    The macro will insert a new named sheet into your workbook "UnPivoted Table" , and then add a table in the new format. With such a table you can do any analysis in LibreOffice Calc.

    Using the extension

    You can also use the utility "Pivot table (Unpivot)" by installing the extension YLC_Utilities.oxt .

    After that, this utility will be available in all files that will be opened in LibreOffice Calc.

    Related Articles:

  • Reorganization of tables (Unpivot) in Excel