A | B | C | D | E | F | G | H | I | J | K | L | |
---|---|---|---|---|---|---|---|---|---|---|---|---|
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 | Name | ID | Lesson 1 | Lesson 2 | Lesson 3 | Lesson 4 | ==> | Name | ID | Lesson | Score | |
5 | A | 1 | 0.9 | 1 | 0.6 | 0.7 | A | 1 | Lesson 1 | 0.9 | ||
6 | B | 2 | 0.8 | 0.9 | 0.5 | 0.6 | A | 1 | Lesson 2 | 1 | ||
7 | C | 3 | 0.9 | 0.7 | 0.4 | 0.5 | A | 1 | Lesson 3 | 0.6 | ||
8 | A | 1 | Lesson 4 | 0.7 | ||||||||
9 | Copy the script below to use the unpivot() function 👇 | B | 2 | Lesson 1 | 0.8 | |||||||
10 | B | 2 | Lesson 2 | 0.9 | ||||||||
11 | B | 2 | Lesson 3 | 0.5 | ||||||||
12 | B | 2 | Lesson 4 | 0.6 | ||||||||
13 | C | 3 | Lesson 1 | 0.9 | ||||||||
14 | C | 3 | Lesson 2 | 0.7 | ||||||||
15 | C | 3 | Lesson 3 | 0.4 | ||||||||
16 | C | 3 | Lesson 4 | 0.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 |