ABCDEFGHIJKL
1
Example (cell H4) =unpivot(A5:F8, 2, 1, "Lesson", "Score")Code by:Viktor Tabori
2
unpivot(data, number of fixed columns, number of fixed rows, title for columns, title for values)
viktor.tabori at gmail
3
https://www.doklist.com/
4
NameIDLesson 1Lesson 2Lesson 3Lesson 4==>NameIDLessonScore
5
A10.910.60.7A1Lesson 10.9
6
B20.80.90.50.6A1Lesson 21
7
C30.90.70.40.5A1Lesson 30.6
8
A1Lesson 40.7
9
Copy the script below to use the unpivot() function 👇B2Lesson 10.8
10
B2Lesson 20.9
11
B2Lesson 30.5
12
B2Lesson 40.6
13
C3Lesson 10.9
14
C3Lesson 20.7
15
C3Lesson 30.4
16
C3Lesson 40.5
17
In your own spreadhseet: menu "Tools" -> "Script editor..."
replace everything with the following script:
18
/**
* 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;
}
19
20
21
22
23