How do you create a "reverse pivot" in Google Sheets?

Question

I am trying to produce a "reverse pivot" function. I have searched long and hard for such a function, but cannot find one that is already out there.

I have a summary table with anywhere up to 20 columns and hundreds of rows, however I would like to convert it into a flat list so I can import to a database (or even use the flat data to create more pivot tables from!)

So, I have data in this format:

	Customer 1	Customer 2	Customer 3
Product 1	1	2	3
Product 2	4	5	6
Product 3	7	8	9

And need to convert it to this format:

 Customer  |  Product  | Qty
-----------+-----------+----
Customer 1 | Product 1 |   1
Customer 1 | Product 2 |   4
Customer 1 | Product 3 |   7
Customer 2 | Product 1 |   2
Customer 2 | Product 2 |   5
Customer 2 | Product 3 |   8
Customer 3 | Product 1 |   3
Customer 3 | Product 2 |   6
Customer 3 | Product 3 |   9

I have created a function that will read the range from sheet1 and append the re-formatted rows at the bottom of the same sheet, however I am trying to get it working so I can have the function on sheet2 that will read the whole range from sheet1.

No matter what I try, I can't seem to get it to work, and was wondering if anybody could give me any pointers?

Here is what I have so far:

function readRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  heads = values[0]
  
  for (var i = 1; i <= numRows - 1; i++) {
    for (var j = 1; j <= values[0].length - 1; j++) {
       var row = [values[i][0], values[0][j], values[i][j]];
       sheet.appendRow(row)
    }
  }
};

Crean

For a more general-purpose solution that works with grouped columns like Customer, Customer type and Customer discount, see my question at How to unpivot data that has multiple column groups in Google Sheets? – Sandlin

Answer 1

I wrote a simple general custom function, which is 100% reusable you can unpivot / reverse pivot a table of any size.

In your case you could use it like this: =unpivot(A1:D4,1,1,"customer","sales")

So you can use it just like any built-in array function in spreadsheet.

Please see here 2 examples: https://docs.google.com/spreadsheets/d/12TBoX2UI_Yu2MA2ZN3p9f-cZsySE4et1slwpgjZbSzw/edit#gid=422214765

The following is the source:

/**
 * 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; i++)
  {
    // skip totally empty or only whitespace containing rows
    if (data[i].join('').replace(/\s+/g,'').length == 0 ) continue;

    // unpivot the row
    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;
}

Harmony

Viktor, in your example with two rows of headers, you still obtain a pivot table. I would like to completely unpivot two rows of headers. Ideally, instead of seeing one column of MIN and one column for MAX, I would like to see those labels as the values of one additional columns. Can your unpivot function be modified? – Swallow

@Swallow there is an easy solution, use unpivot twice (2nd tab G13 cell). You can stack the unpivot function just like any other function in spreadsheet: docs.google.com/spreadsheets/d/… – Harmony

The titlePivot / titleValue parameters appear unused.. What am I missing? – Bacteriostat

It is used but through the argument variable, thus the default fallback for the value is indeed not working. Yet. 😅 – Harmony

Answer 2

That is basically array manipulation... below is a code that does what you want and writes back the result below existing data.

You can of course adapt it to write on a new sheet if you prefer.

function transformData(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();//read whole sheet
  var output = [];
  var headers = data.shift();// get headers
  var empty = headers.shift();//remove empty cell on the left
  var products = [];
    for(var d in data){
      var p = data[d].shift();//get product names in first column of each row
      products.push(p);//store
    }
  Logger.log('headers = '+headers);
  Logger.log('products = '+products);
  Logger.log('data only ='+data);
  for(var h in headers){
    for(var p in products){  // iterate with 2 loops (headers and products)
      var row = [];
      row.push(headers[h]);
      row.push(products[p]);
      row.push(data[p][h])
      output.push(row);//collect data in separate rows in output array
    }
  }
  Logger.log('output array = '+output);
  sheet.getRange(sheet.getLastRow()+1,1,output.length,output[0].length).setValues(output);
}

to automatically write the result in a new sheet replace last line of code with these :

  var ns = SpreadsheetApp.getActive().getSheets().length+1
  SpreadsheetApp.getActiveSpreadsheet().insertSheet('New Sheet'+ns,ns).getRange(1,1,output.length,output[0].length).setValues(output);

Harlamert

Answer 3

google-sheets-formula

With the advent of new LAMBDA and MAKEARRAY functions, we can unpivot the data without string manipulation. This works by creating a sequence of appropriate index numbers for the new array, which should be faster than string manipulation.

=ARRAYFORMULA(LAMBDA(range,s_cols,
  QUERY(
    MAKEARRAY(ROWS(range)*(COLUMNS(range)-s_cols),s_cols+1,
      LAMBDA(i,j,
        TO_TEXT(
          INDEX(range,
            ROUNDDOWN(1+(i-1)/(COLUMNS(range)-s_cols)),
            if(j>s_cols,MOD(i-1,COLUMNS(range)-s_cols)+s_cols+1,j)
          )
        )
      )
    ),"where Col"&s_cols+1&" is not null"
  )
)(A1:C10,2))

Or as a named function(UNPIVOT(range,s_cols)):

=ARRAYFORMULA(
  QUERY(
    MAKEARRAY(ROWS(range)*(COLUMNS(range)-s_cols),s_cols+1,
      LAMBDA(i,j,
        TO_TEXT(
          INDEX(range,
            ROUNDDOWN(1+(i-1)/(COLUMNS(range)-s_cols)),
            if(j>s_cols,MOD(i-1,COLUMNS(range)-s_cols)+s_cols+1,j)
          )
        )
      )
    ),"where Col"&s_cols+1&" is not null"
  )
)

Arguments:

• range: The range to unpivot. Eg:A1:C10
• s_cols: The number of static columns on the left.Eg:2

---

google-apps-script

Using simple, yet powerful loops on V8 engine:

/**
 * Unpivots the given data
 *
 * @return Unpivoted data from array
 * @param {A1:C4} arr 2D Input Array
 * @param {1=} ignoreCols [optional] Number of columns on the left to ignore
 * @customfunction
 */
const unpivot = (arr, ignoreCols = 1) =>
  ((j, out) => {
    while (++j < arr[0].length)
      ((i) => {
        while (++i < arr.length)
          out.push([arr[0][j], ...arr[i].slice(0, ignoreCols), arr[i][j]]);
      })(0);
    return out;
  })(ignoreCols - 1, []);

Usage:

=UNPIVOT(A1:C4)
=UNPIVOT(A1:F4,3)//3 static cols on left
={{"Customer","Products","Qty"};UNPIVOT(A1:D4)}//add headers

Live demo:

/*<ignore>*/console.config({maximize:true,timeStamps:false,autoScroll:false});/*</ignore>*/
const arr = [
  ['          ', ' Customer 1 ', ' Customer 2 ', ' Customer 3'],
  ['Product 1 ', '          1 ', '          2 ', '          3'],
  ['Product 2 ', '          4 ', '          5 ', '          6'],
  ['Product 3 ', '          7 ', '          8 ', '          9'],
];
console.log("Input table")
console.table(arr)
/**
 * Unpivots the given data
 *
 * @return Unpivoted data from array
 * @param {A1:C4} arr 2D Input Array
 * @param {1=} ignoreCols [optional] Number of columns on the left to ignore
 * @customfunction
 */
const unpivot = (arr, ignoreCols = 1) =>
  ((j, out) => {
    while (++j < arr[0].length)
      ((i) => {
        while (++i < arr.length)
          out.push([arr[0][j], ...arr[i].slice(0, ignoreCols), arr[i][j]]);
      })(0);
    return out;
  })(ignoreCols - 1, []);
console.log("Output table")
console.table(unpivot(arr));
console.log("Output table with 2 static columns")
console.table(unpivot(arr,2));

<!-- https://meta.stackoverflow.com/a/375985/ -->    <script src="https://gh-canon.github.io/stack-snippet-console/console.min.js"></script>

---

Check history for older deprecated functions

Hookup

Answer 4

Use FLATTEN. It converts any array into single column.

Here's the formula for unpivot:

=ARRAYFORMULA(SPLIT(FLATTEN(A2:A12&"💣"&B1:F1&"💣"&B2:F12),"💣"))

FLATTEN creates 1-column array of Item1💣Date1💣67455 strings, which we then split.

Please copy the sample file to try.

Shorter:

=index(SPLIT(FLATTEN(A2:A12&"💣"&B1:F1&"💣"&B2:F12),"💣"))

---

Please also see this solution.

It uses INDIRECT and settings, so the formula looks like a more general solution:

Caught

It looks like the FLATTEN function is now official :) There's a support page and it appears in formula completion: support.google.com/docs/answer/10307761 – Colombo

Does it work with any emoji 🤡? – Bartolemo

Answer 5

I didn't think you had enough array formula answers so here's another one.

Test Data (Sheet 1)

Formula for customer

=ArrayFormula(hlookup(int((row(indirect("1:"&Tuples))-1)/Rows)+2,{COLUMN(Sheet1!$1:$1);Sheet1!$1:$1},2))

(uses a bit of math to make it repeat and hlookup to find correct column in column headers)

Formula for product

=ArrayFormula(vlookup(mod(row(indirect("1:"&Tuples))-1,Rows)+2,{row(Sheet1!$A:$A),Sheet1!$A:$A},2))

(similar approach using mod and vlookup to find correct row in row headers)

Formula for quantity

=ArrayFormula(vlookup(mod(row(indirect("1:"&Tuples))-1,Rows)+2,{row(Sheet1!$A:$A),Sheet1!$A:$Z},int((row(indirect("1:"&Tuples))-1)/Rows)+3))

(extension of above approach to find both row and column in 2d array)

Then combining these three formulas into a query to filter out any blank values for quantity

=ArrayFormula(query(
   {hlookup(int((row(indirect("1:"&Tuples))-1)/Rows)+2, {COLUMN(Sheet1!$1:$1);Sheet1!$1:$1},2),
    vlookup(mod(row(indirect("1:"&Tuples))-1,Rows)+2,{row(Sheet1!$A:$A),Sheet1!$A:$A},2),
    vlookup(mod(row(indirect("1:"&Tuples))-1,Rows)+2,{row(Sheet1!$A:$A),Sheet1!$A:$Z},int((row(indirect("1:"&Tuples))-1)/Rows)+3)},
"select * where Col3 is not null"))

Note

The named ranges Rows and Cols are obtained from the first column and row of the data using counta and Tuples is their product. The separate formulas

=counta(Sheet1!A:A)

=counta(Sheet1!1:1)

and

=counta(Sheet1!A:A)*counta(Sheet1!1:1)

could be included in the main formula if required with some loss of readability.

---

For reference, here is the 'standard' split/join solution (with 50K data limit) adapted for the present situation:

=ArrayFormula(split(transpose(split(textjoin("♫",true,transpose(if(Sheet1!B2:Z="","",Sheet1!B1:1&"♪"&Sheet1!A2:A&"♪"&Sheet1!B2:Z))),"♫")),"♪"))

This is also fairly slow (processing 2401 array elements). If you restrict the computation to the actual dimensions of the data, it is much faster for small datasets:

=ArrayFormula(split(transpose(split(textjoin("♫",true,transpose(if(Sheet1!B2:index(Sheet1!B2:Z,counta(Sheet1!A:A),counta(Sheet1!1:1))="","",Sheet1!B1:index(Sheet1!B1:1,counta(Sheet1!1:1))&"♪"&Sheet1!A2:index(Sheet1!A2:A,counta(Sheet1!A:A))&"♪"&Sheet1!B2:index(Sheet1!B2:Z,counta(Sheet1!A:A),counta(Sheet1!1:1))))),"♫")),"♪"))

Bayne

Thing 1: This is great. Thing 2: How did you know that Col3 was able to identify the third column? Is there some documentation somewhere? I was stymied here until seeing your answer to this question. – Panache

In answer to @Michael, 1. Thanks! 2. I can't seem to find a reference to it in Google's documentation, but it's been out there for a while that you can use Col1 to reference the first column of an array etc e.g. support.google.com/docs/forum/AAAABuH1jm0wYw_co2pMNQ/… – Bayne

Answer 6

=ARRAYFORMULA({"Customer", "Product", "Qty"; 
 QUERY(TRIM(SPLIT(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF(B2:Z<>"", B1:1&"♠"&A2:A&"♠"&B2:Z&"♦", )), , 999^99)), , 999^99)), "♦")), "♠")), 
 "where Col1<>'' order by Col1")})

Halyard

Nice Solution. Have you turned on iterative calculation? Your formula should be pasted on another sheet for it to work without it or you'll get circular reference errors. – Hookup

Answer 7

Here another alternative:

=arrayformula
(
   { "PRODUCT","CUSTOMER","QTY";
     split 
     ( transpose ( split 
                   ( textjoin("✫" ,false,filter(Sheet2!A2:A,Sheet2!A2:A<>"") & "✤" &
                              filter(Sheet2!B1:1,Sheet2!B1:1<>""))
                     ,"✫",true,false)),"✤",true,false
     ),
     transpose ( split ( textjoin ( "✤", false, transpose ( filter 
     ( 
       indirect( "Sheet2!B2:"  & MID(address(1,COUNTA( Sheet2!B1:1)+1), 2,
                                     FIND("$",address(1,COUNTA( Sheet2!B1:1)+1),2)-2)
               )   
       , Sheet2!A2:A<>""
       ))),"✤",true,false)
     )
   }
 )

Explanation:

1. "PRODUCT","CUSTOMER","QTY"
   -- Use for giving title

2. split 
   ( transpose ( split 
               ( textjoin("✫" ,false,filter(Sheet2!A2:A,Sheet2!A2:A<>"") & "✤" &
                          filter(Sheet2!B1:1,Sheet2!B1:1<>""))
               ,"✫",true,false)),"✤",true,false
   )
   -- Use for distributing Row1 and ColumnA, to be Product and Customer Columns

3. transpose ( split ( textjoin ( "✤", false, transpose ( filter 
   ( 
     indirect( "Sheet2!B2:"  & MID(address(1,COUNTA( Sheet2!B1:1)+1), 2,
                                 FIND("$",address(1,COUNTA( Sheet2!B1:1)+1),2)-2)
             )   
     , Sheet2!A2:A<>""
     ))),"✤",true,false)
   )
   --use to distributed data qty to Qty Column

Sheet2 Pict:

Result Sheet Pict:

Carminecarmita

Answer 8

Input Sheet

This function will handle many customers and many products and it will sum the quantities of multiple customer/product entries and summarize it into one simple table.

The Code:

function rPVT() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Sheet1');
  var osh=ss.getSheetByName('Sheet2');
  osh.clearContents();
  var vA=sh.getDataRange().getValues();
  var itoh={};
  var pObj={};
  vA[0].forEach(function(h,i){if(h){itoh[i]=h;}});
  for(var i=1;i<vA.length;i++) {
    for(var j=1;j<vA[i].length;j++) {
      if(!pObj.hasOwnProperty(itoh[j])){pObj[itoh[j]]={};}
      if(!pObj[itoh[j]].hasOwnProperty(vA[i][0])){pObj[itoh[j]][vA[i][0]]=vA[i][j];}else{pObj[itoh[j]][vA[i][0]]+=(vA[i][j]);}
    }
  }
  var oA=[['Customer','Product','Quantity']];  
  Object.keys(pObj).forEach(function(ik){Object.keys(pObj[ik]).forEach(function(jk){oA.push([ik,jk,pObj[ik][jk]]);});});
  osh.getRange(1,1,oA.length,oA[0].length).setValues(oA);
}

Output Sheet:

The following function reads Sheet2 which is the output of the above function and returns it to the original format.

function PVT() {
  var ss=SpreadsheetApp.getActive();
  var sh2=ss.getSheetByName('Sheet2');
  var sh3=ss.getSheetByName('Sheet3');
  sh3.clearContents();
  var vA=sh2.getRange(2,1,sh2.getLastRow()-1,sh2.getLastColumn()).getValues();
  pObj={};
  vA.forEach(function(r,i){if(!pObj.hasOwnProperty(r[1])){pObj[r[1]]={};}if(!pObj[r[1]].hasOwnProperty(r[0])){pObj[r[1]][r[0]]=r[2];}else{pObj[r[1]][r[0]]+=r[2];}});
  var oA=[];
  var ikeys=Object.keys(pObj);
  var jkeys=Object.keys(pObj[ikeys[0]]);
  var hkeys=jkeys.slice();
  hkeys.unshift(''); 
  oA.push(hkeys);
  ikeys.forEach(function(ik,i){var row=[];row.push(ik);jkeys.forEach(function(jk,j){row.push(pObj[ik][jk]);});oA.push(row);});
  sh3.getRange(1,1,oA.length,oA[0].length).setValues(oA);
}

Quietus

Answer 9

If your data has a single unique key column, this spreadsheet may have what you need.

Your unpivot sheet will contain:

• The key column =OFFSET(data!$A$1,INT((ROW()-2)/5)+1,0)
• The column header column =OFFSET(data!$A$1,0,IF(MOD(ROW()-1,5)=0,5,MOD(ROW()-1,5)))
• The cell value column =INDEX(data!$A$1:$F$100,MATCH(A2,data!$A$1:$A$100,FALSE),MATCH(B2,data!$A$1:$F$1,FALSE))

where 5 is the number of columns to unpivot.

---

I did not make the spreadsheet. I happened across it in the same search that led me to this question.

Panache

Answer 10

One range refrence

This will work regardless of the number of customers and products. with one range reference in this case (A1:D4)

=ArrayFormula({SPLIT("Customer|Product|Qty","|");
 QUERY(LAMBDA(r,SPLIT(FLATTEN(
 QUERY({r}, " Select Col1 ", 1)&"+"&
 QUERY({r}, " select "& TEXTJOIN(",",1,REGEXREPLACE("Col#", "#", SEQUENCE(COLUMNS(QUERY(r, " select * limit 0 ", 1))-1,1,2,1)&""))&" limit 0 ", 1)&"+"&
 QUERY({QUERY({r}, " Select "& TEXTJOIN(",",1,REGEXREPLACE("Col#", "#", SEQUENCE(COLUMNS(QUERY({r}, " select * where Col1 <> '' ", 1))-1,1,2,1)&""))&" ", 0)},
 " Select * where Col1 is not null ")),"+"))(A1:D4)," Select * Where Col2 <> '' ")})

Demonstration

This woks well when you have this table "on the left" as an output of another formula.
in this case simulated with the range A1:G15

20 columns and hundreds of rows

Named function

Pending...

Used formulas help
ARRAYFORMULA - SPLIT - QUERY - LAMBDA - FLATTEN - TEXTJOIN
REGEXREPLACE - SEQUENCE - COLUMNS - NOT

Bartolemo

Answer 11

Here is option with LET

=LET(
  data, A1:F4,
  first_cols_numb,2,
  rows, ROWS(data),
  cols, COLUMNS(data),
  first_cols, INDEX(data,2,1):INDEX(data,rows,first_cols_numb),
  first_cols_concat, ARRAYFORMULA(TEXTJOIN("😎", TRUE, first_cols)),
  header, INDEX(data, 1, first_cols_numb + 1):INDEX(data,1,cols),
  body, INDEX(data, 2, first_cols_numb + 1):INDEX(data,rows, cols),
  answ, ARRAYFORMULA(SPLIT(FLATTEN(first_cols_concat&"😎"&header&"😎"&body),"😎")),
  answ
)

Christian

Answer 12

Approach 1

=reduce(tocol(,1),sequence(columns(B1:D1)),lambda(a,c,vstack(a,let(Σ,hstack(let(Λ,index(B1:D1,c),wrapcols(Λ,rows(A2:A),Λ)),A2:A,choosecols(B2:D,c)),filter(Σ,index(Σ,,3)<>"")))))

Approach 2

=let(Σ,tocol(,1),choosecols(sort(reduce(Σ,B2:index(D:D,match(,0/(A:A<>""))),lambda(a,c,vstack(if(iserr(+a),Σ,a),if(c="",Σ,hstack(index(1:1,column(c)),index(A:A,row(c)),c,column(c)))))),4,1),sequence(3)))

Spate