Home / Questions / 6091
Accepted
Rubén
Asked: 2020-03-21 21:19:10 +0800 CST 2020-03-21 21:19:10 +0800 CST

Convert tabular report (matrix) to simple table format

  • 772

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

+---+------------+-----------+-----------+-------- ---+
| | To | B | C | D |
+---+------------+-----------+-----------+-------- ---+
| 1 | | Client 1 | Client 2 | Client 3 |
| 2 | Product 1 | 4 | 2 | 6 |
| 3 | Product 2 | 7 | 8 | 6 |
| 4 | Product 3 | 6 | 3 | 5 |
| 5 | Product 4 | 3 | 3 | 8 |
+---+------------+-----------+-----------+-------- ---+

I require to pass the data to plain table format, i.e. one column for row headers, one column for column headers and one column for values, as shown below:

+----+---------------------+---------------------- --+-----+
| | To | B | C |
+----+---------------------+---------------------- --+-----+
| 1 | Row Headers | Column headers | Values ​​|
| 2 | Product 1 | Client 1 | 4 |
| 3 | Product 2 | Client 2 | 8 |
| 4 | Product 3 | Client 3 | 5 |
| 5 | Product 4 | Client 1 | 3 |
| 6 | Product 1 | Client 2 | 2 |
| 7 | Product 2 | Client 3 | 6 |
| 8 | Product 3 | Client 1 | 6 |
| 9 | Product 4 | Client 2 | 3 |
| 10 | Product 1 | Client 3 | 6 |
| 11 | Product 2 | Client 1 | 7 |
| 12 | Product 3 | Client 2 | 3 |
| 13 | Product 4 | Client 3 | 8 |
+----+---------------------+---------------------- --+-----+

How can I achieve this using google spreadsheet functions?

Clarifications

Question inspired by How do you create a “reverse pivot” in Google Sheets? - Stack Overflow and personal experience.

google-hoja-de-cálculo

4 Answers

  1. Best Answer

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

    1. Create a sheet and name it "Aux".
    2. In the sheet created in the previous step, add the following formulas: It is assumed that the tabular report data is found in a sheet called data, that cell A1 is blank and that it does not include data from outside 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.
    3. Create a new sheet
    4. 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)},
    two
  )
)

    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)+1determines the index of the row header.

    MOD(ROW(INDIRECT("A1:A"&Aux!A1*Aux!A2))-1,Aux!A2)+1determines 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 of the tabular report.

    • 1

  2. I have put together 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++;
    }
  }
}

    Save the code file. Every time someone modifies the "Original" sheet, the "Destination" sheet will be updated.

    Hope this can help you.

    I found the question very interesting from a didactic point of view, so I used it to publish on my blog... I hope you don't mind: http://googleappscriptsweb.blogspot.com.es/2016/06/como- convert-a-data-array-to.html

    • 1

  3. 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 credit to Viktor Camp

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

    https://stackoverflow.com/questions/24954722/how-do-you-create-a-reverse-pivot-in-google-sheets

    /**
* 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;
}
    • 1

  4. All are reasonable solutions, in my case I had a data table with 19 columns and 1600 rows and the "unpivot" tool of the HdCPlus plugin worked very well, which is open source and has other interesting utilities. https://workspace.google.com/marketplace/app/hdc+/410659432888

    • -1