Convert tabular report (matrix) to simple table format

Navigation
3

I have a tabular report, also called a matrix or "crosstab". For example, in the column headers, the name of the customer is indicated, in the headings of the lines the name of the product and in the intersection the number of products sold to each customer. 

+---+------------+-----------+-----------+-----------+
|   |     A      |     B     |     C     |     D     |
+---+------------+-----------+-----------+-----------+
| 1 |            | Cliente 1 | Cliente 2 | Cliente 3 |
| 2 | Producto 1 | 4         | 2         | 6         |
| 3 | Producto 2 | 7         | 8         | 6         |
| 4 | Producto 3 | 6         | 3         | 5         |
| 5 | Producto 4 | 3         | 3         | 8         |
+---+------------+-----------+-----------+-----------+

I need to pass the data to a simple table format, that is, a column for row headers, a column for column headings and a column for values, as shown below: 

+----+---------------------+------------------------+---------+
|    |          A          |           B            |    C    |
+----+---------------------+------------------------+---------+
|  1 | Encabezados de fila | Encabezados de columna | Valores |
|  2 | Producto 1          | Cliente 1              | 4       |
|  3 | Producto 2          | Cliente 2              | 8       |
|  4 | Producto 3          | Cliente 3              | 5       |
|  5 | Producto 4          | Cliente 1              | 3       |
|  6 | Producto 1          | Cliente 2              | 2       |
|  7 | Producto 2          | Cliente 3              | 6       |
|  8 | Producto 3          | Cliente 1              | 6       |
|  9 | Producto 4          | Cliente 2              | 3       |
| 10 | Producto 1          | Cliente 3              | 6       |
| 11 | Producto 2          | Cliente 1              | 7       |
| 12 | Producto 3          | Cliente 2              | 3       |
| 13 | Producto 4          | Cliente 3              | 8       |
+----+---------------------+------------------------+---------+

How can I achieve this using Google's spreadsheet functions?

Clarifications

Inspired question How do you create a "reverse pivot" in Google Sheets? - Stack Overflow and in personal experience.

    
asked by Rubén 21.03.2016 в 06:19
source

3 answers

1

In this file you will find an example of how to perform the conversion. Here are the steps to follow.

  • Create a sheet and call it "Aux".
  • On the sheet created in the previous step add the following formulas: It is assumed that the tabular report data is in a sheet called data , that cell A1 is blank and that it does not include data unrelated to the report.
    A1: =COUNTA(data!A:A) Number of rows.
    A2: =COUNTA(data!1:1) Number of columns.
    A3: =CELL("address",data!A1) Intermediate calculation.
    A4: =LEFT(A3,FIND("!",A3)-1) Name of the sheet with the source data.
  • Create a new sheet
  • On the new sheet add the following
    A1: Row headers
    A2: 
    • =ArrayFormula(
  VLOOKUP(
    MOD(ROW(INDIRECT("A1:A"&Aux!A1*Aux!A2))-1,Aux!A1)+1+1,
    {(ROW(INDIRECT("A1:A"&Aux!A1+1))),INDIRECT(Aux!A4&"!R1C1:R"&Aux!A1+1&"C"&Aux!A2+1,false)},
    2
  )
)

    B1: Column headers
    B2: 

    =ArrayFormula(
  VLOOKUP(
    SIGN(ROW(INDIRECT("A1:A"&Aux!A1*Aux!A2))),
    {(ROW(INDIRECT("A1:A"&Aux!A1+1))),INDIRECT(Aux!A4&"!R1C1:R"&Aux!A1+1&"C"&Aux!A2+1,false)},
    MOD(ROW(INDIRECT("A1:A"&Aux!A1*Aux!A2))-1,Aux!A2)+1+2
  )
)

    C1: Values
    C2: 

    =ArrayFormula(
  VLOOKUP(
    MOD(ROW(INDIRECT("A1:A"&Aux!A1*Aux!A2))-1,Aux!A1)+1+1,
    {(ROW(INDIRECT("A1:A"&Aux!A1+1))),INDIRECT(Aux!A4&"!R1C1:R"&Aux!A1+1&"C"&Aux!A2+1,false)},
    MOD(ROW(INDIRECT("A1:A"&Aux!A1*Aux!A2))-1,Aux!A2)+1+2
  )
)

    Description of the constructs used

    ROW(INDIRECT("A1:A"&Aux!A1*Aux!A2)) determines an array of consecutive numbers whose size corresponds to the number of rows required in the final result.

    MOD(ROW(INDIRECT("A1:A"&Aux!A1*Aux!A2))-1,Aux!A1)+1 determines the index of the row header.

    MOD(ROW(INDIRECT("A1:A"&Aux!A1*Aux!A2))-1,Aux!A2)+1 determines the index of the column header.

    {(ROW(INDIRECT("A1:A"&Aux!A1+1))),INDIRECT(Aux!A4&"!R1C1:R"&Aux!A1+1&"C"&Aux!A2+1,false)} creates a matrix whose first column is the index of the row and the following corresponds to the data in the tabular report.

        
    answered by 21.03.2016 / 06:19
    source
    1

    I have set up a solution to your problem with google apps script that I think is simpler than the one proposed so far.

    In the code editor of the sheet put the following: 

    function onOpen() 
{
    Convert();
};

function onEdit() 
{
    Convert();
};

function Convert()
{
  var spread = SpreadsheetApp.getActive();
  var sheet_ori = spread.getSheetByName("Original");
  var sheet_dst = spread.getSheetByName("Destino");

  var values_ori = sheet_ori.getDataRange().getValues();

  var names = values_ori[0];
  var cont = 1;

  for(var i = 1 ; i < values_ori.length ; ++i)
  { 
    var row = values_ori[i];
    var caption = row[0];
    for(var j = 1 ; j < row.length ; ++j)
    {
        sheet_dst.getRange(cont, 1).setValue(caption);
        sheet_dst.getRange(cont, 2).setValue(names[j]);
        sheet_dst.getRange(cont, 3).setValue(row[j]);
        cont++;
    }
  }
}

    Record the code file. Every time someone changes the "Original" sheet, the "Destination" sheet will be updated.

    I hope it serves you.

    The question has been very interesting for me from a didactic point of view, so I used it to post on my blog ... I hope it does not bother you: link

        
    answered by 11.06.2016 в 15:04
    1

    I had the same problem but I found a solution that I already applied with a script. Here comes everything how to do it and I give the respective merit to Viktor Camp

    I suggest you see the link and there it takes you to the example and everything very well explained

      

    link 

    /**
* Unpivot a pivot table of any size.
*
* @param {A1:D30} data The pivot table.
* @param {1} fixColumns Number of columns, after which pivoted values begin. 
Default 1.
* @param {1} fixRows Number of rows (1 or 2), after which pivoted values 
begin. Default 1.
* @param {"city"} titlePivot The title of horizontal pivot values. Default 
"column".
* @param {"distance"[,...]} titleValue The title of pivot table values. 
Default "value".
* @return The unpivoted table
* @customfunction
*/
function unpivot(data,fixColumns,fixRows,titlePivot,titleValue) {  
var fixColumns = fixColumns || 1; // how many columns are fixed
var fixRows = fixRows || 1; // how many rows are fixed
var titlePivot = titlePivot || 'column';
var titleValue = titleValue || 'value';
var ret=[],i,j,row,uniqueCols=1;

// we handle only 2 dimension arrays
if (!Array.isArray(data) || data.length < fixRows || !Array.isArray(data[0]) 
|| data[0].length < fixColumns)
throw new Error('no data');
// we handle max 2 fixed rows
  if (fixRows > 2)
    throw new Error('max 2 fixed rows are allowed');

  // fill empty cells in the first row with value set last in previous 
columns (for 2 fixed rows)
  var tmp = '';
  for (j=0;j<data[0].length;j++)
    if (data[0][j] != '') 
      tmp = data[0][j];
    else
      data[0][j] = tmp;

  // for 2 fixed rows calculate unique column number
  if (fixRows == 2)
  {
    uniqueCols = 0;
    tmp = {};
    for (j=fixColumns;j<data[1].length;j++)
      if (typeof tmp[ data[1][j] ] == 'undefined')
      {
        tmp[ data[1][j] ] = 1;
        uniqueCols++;
      }
  }

  // return first row: fix column titles + pivoted values column title + 
values column title(s)
  row = [];
    for (j=0;j<fixColumns;j++) row.push(fixRows == 2 ? data[0][j]||data[1] 
[j] : data[0][j]); // for 2 fixed rows we try to find the title in row 1 and 
row 2
    for (j=3;j<arguments.length;j++) row.push(arguments[j]);
  ret.push(row);

  // processing rows (skipping the fixed columns, then dedicating a new row 
for each pivoted value)
  for (i=fixRows;i<data.length && data[i].length > 0 && data[i][0];i++)
  {
    row = [];
    for (j=0;j<fixColumns && j<data[i].length;j++)
      row.push(data[i][j]);
    for (j=fixColumns;j<data[i].length;j+=uniqueCols)
      ret.push( 
        row.concat([data[0][j]]) // the first row title value
        .concat(data[i].slice(j,j+uniqueCols)) // pivoted values
      );
  }

  return ret;
}
        
    answered by 12.06.2018 в 00:27
    Access controls within a Xaml HUB and c # LocalStorage does not keep the data saved when the browser is closed