How to unpivot data from a matrix to a vector?

Question

I have a sheet structured like this:

+---------------------------+
|date  | jan 1 | jan2 |jan3 |
+---------------------------+
|empl1 | 3     |  5   |8    |
+---------------------------+
|empl2 | 4     |  7   |6    |
+---------------------------+
|empl3 | 1     |  2   |9    |
+---------------------------+

Which I would like to unpivot so it looks like this:

+------+-------+--------
|empl1 | jan 1 |   3   |
+----------------------+
|empl2 | jan 1 |   4   |
+----------------------+
|empl3 | jan 1 |   1   |
+----------------------+
|empl1 | jan2  |   5   |
+----------------------+
|empl2 | jan2  |   7   |
+----------------------+
|empl3 | jan2  |   2   |
+----------------------+
|empl1 | jan3  |   8   |
+----------------------+
|empl2 | jan3  |   6   |
+----------------------+
|empl3 | jan3  |   9   |
+------+-------+--------

Is there a way to acheive this with formulae, if so, how? Or can this only be acheived through script?

Any help or nudge in the correct direction would be much appreciated.

Answer 1

Method 1: Using built-in functions:

=ARRAYFORMULA({SPLIT(TRANSPOSE(SPLIT(TEXTJOIN(":",FALSE,FILTER(A2:A&","&B1:D1&","&B2:D,A2:A<>"")),":",TRUE,FALSE)),",",TRUE,FALSE)})

Method 2: Using Apps Script Custom function:

You could do this with an Apps Script Custom Function.

First, open a bound script by selecting Tools > Script editor, and copy the following function to the script (check inline comments):

function matrixToVector(values) {
  var output = [];
  var headers = values.shift(); // Remove and retrieve the headers row  
  for (var i = 1; i < values[0].length; i++) { // Iterate through each column
    for (var j = 0; j < values.length; j++) { // Iterate through each row
      output.push([values[j][0], headers[i], values[j][i]]);
    }
  }
  return output;
};

Once it is defined, you can use this function the same you would any sheets built-in function:

Reference:

• Custom Functions in Google Sheets