Search code examples
google-apps-scriptgoogle-sheetsunpivotcustom-function

Formula to array all columns with first column

I am pulling in csv data into a Google Spreadsheet which has names running vertically in column A and questions running horizontally in row 1. There are scores in some of the columns and others are blank. Like Such:

 |       | Q1  | Q2  | Q3 | Q4  | Q5  | Q6 | Q7  |
 |-------|-----|-----|----|-----|-----|----|-----|
 | Name1 | ##% | ##% |    | ##% | ##% |    | ##% |
 | Name2 | ##% | ##% |    | ##% | ##% |    | ##% |
 | Name3 | ##% | ##% |    | ##% | ##% |    | ##% |
 | Name4 | ##% | ##% |    | ##% | ##% |    |     |
 | Name5 | ##% | ##% |    | ##% | ##% |    |     |

I would like to build a formula to match up each columns scores with the names column and the column header into just 3 columns regardless of weather there are any values in the cells. I would Like a names column, a question title column, and a score column like the following:

 | Name1 | Q1 | ##% |
 | Name2 | Q1 | ##% |
 | Name3 | Q1 | ##% |
 | Name4 | Q1 | ##% |
 | Name5 | Q1 | ##% |
 | Name1 | Q2 | ##% |
 | Name2 | Q2 | ##% |
 | Name3 | Q2 | ##% |
 | Name4 | Q2 | ##% |
 | Name5 | Q2 | ##% |
 | Name1 | Q3 | ##% |
 | Name2 | Q3 |     |
 | Name3 | Q3 |     |
 | Name4 | Q3 |     |
 | Name5 | Q3 |     |
 And so on....

Currently I am doing this by in cell A1 of a new sheet arraying the first column over and over like:

= {FILTER(Input!A:A,Input!A:A<>"");FILTER(Input!A:A,Input!A:A<>"");FILTER(Input!A:A,Input!A:A<>"");FILTER(Input!A:A,Input!A:A<>"");FILTER(Input!A:A,Input!A:A<>""); FILTER(Input!A:A,Input!A:A<>"");FILTER(Input!A:A,Input!A:A<>"");FILTER(Input!A:A,Input!A:A<>"");FILTER(Input!A:A,Input!A:A<>"");FILTER(Input!A:A,Input!A:A<>"")};

and then arraying all the columns like this in Cell B2: ={FILTER(Input!B:B,Input!A:A<>"");FILTER(Input!C:C,Input!A:A<>"");FILTER(Input!D:D,Input!A:A<>"");FILTER(Input!E:E,Input!A:A<>"");FILTER(Input!F:F,Input!A:A<>""); FILTER(Input!G:G,Input!A:A<>"");FILTER(Input!H:H,Input!A:A<>"");FILTER(Input!I:I,Input!A:A<>"");FILTER(Input!J:J,Input!A:A<>"");FILTER(Input!K:K,Input!A:A<>"");}

I have a formula I have dragged down column C to find the header rows in the array and copy them down for all the names.

Since I have hundreds of columns of questions, these array formulas are huge. Is there a simpler way to do this by simply referencing all the columns with some kind of matrix?

Any help is appreciated.

Solution

  • I have a sample script for this kind of tasks. It may be used as custom formula:

    =unpivotTable(false, B1, B2, B3)

    where

    1. false to use current file
    2. B1 holds first header range: Sheet1!A1
    3. B2 holds second header range: Sheet1!B1:Z1
    4. B3 holds headers for pivoted columns: Question,%

    Plese try if my script fits you:

    Sample file to explore.