Google Sheets – How to Transpose Ranges Repeated by Row

google sheetsgoogle-sheets-queryunpivot

I would like to convert several range of columns into rows, and keeping the association to the original col/row source

Sample source sheet

---------------------------------------------------
| Data 1 | 1/1/2018 | 1/2/2018 | 1/3/2018 | etc...365 days |
---------------------------------------------------
| name 1  |    25    |   30     |    5     | etc...| 
---------------------------------------------------
| name 2  |    2     |          |    4     | etc...|
---------------------------------------------------
| etc ... 100 rows

Expected Results

---------------------------------------------------
| Data 1 |   Date      |  Value   | 
---------------------------------------------------
| name 1  |  1/1/2018   |   25     |   
---------------------------------------------------
| name 1  |  1/2/2018   |   30     |   
---------------------------------------------------
| name 1  |  1/3/2018   |   5      |   
---------------------------------------------------
| name 1  |  etc.. 365 days
---------------------------------------------------
| name 2  |  1/1/2018   |   2      |   
---------------------------------------------------
| name 2  |  1/2/2018   |          |   
---------------------------------------------------
| name 2  |  1/3/2018   |   4      |   
---------------------------------------------------
| name 2  |  etc.. 365 days
---------------------------------------------------
| etc ... for each change of name pull all the col dates/values

Current results

---------------------------------------------------
| Data 1 |   Date      |  Value   | 
---------------------------------------------------
| name 1  |  1/1/2018   |   25     |   
---------------------------------------------------
|         |  1/2/2018   |   30     |   
---------------------------------------------------
|         |  1/3/2018   |   5      |   
---------------------------------------------------
|         |  etc.. 365 days                      I have to copy down to bring in "name 1" for each date 
---------------------------------------------------
| name 2  |  1/1/2018   |   2      |             now update the formula to bring in the second item for the sequence of dates
---------------------------------------------------
|         |  1/2/2018   |          |   
---------------------------------------------------
|         |  1/3/2018   |   4      |   
---------------------------------------------------
|         |  etc.. 365 days
---------------------------------------------------
| etc ...

=Sheet1!A:A I can bring in the name from the source list, but I have to copy it down for the number of dates. How can I have it match the number of dates? and be dynamic to update as dates/values are added
Edit: trying to use split and rept to count the number of times to repeat, then transpose.. =transpose(rept(split(arrayformula(concatenate(Sheet1!A:A&"1")),"1",True, True),COUNTA(Sheet1!I1:ZZ1)))

=TRANSPOSE(Sheet1!$C$1:$Z$1) I can use transpose to get the dates/values, but it's fixed range. How can I make it dynamic to import dates as they are added?

Best Answer

Nowadays there is a simpler way.

=ARRAYFORMULA(SPLIT(FLATTEN(<row headers>&"|"&<column headers>&"|"&<values area>),"|"))

For the OP case, 101 names , 365 days

=ARRAYFORMULA(SPLIT(FLATTEN(Sheet1!A2:A101&"|"&Sheet1!A1:MA1"&"|"&Sheet1!B2:MA101),"|"))

Just add the headers above the formula.

Ref. answer to How do you create a “reverse pivot” in Google Sheets?

=Sheet1!A:A I can bring in the name from the source list, but I have to copy it down for the number of dates. How can I have it match the number of dates?

If you are looking for a formula-based solution the following formula will work for the first "name" row

(for testing purposes, it only includes the first 31 days, but you could easily change AF by ZZ)

=ArrayFormula(SPLIT(TRANSPOSE(Sheet1!B1:AF1)&"|"&TRANSPOSE(Sheet1!A2&"|"&Sheet1!B2:AF2),"|"))

If you are able to work with large formulas you could create an array repeating the SPLIT function and its arguments, once by each "name" row, in the following way:

=ArrayFormula({ SPLIT(TRANSPOSE(Sheet1!B1:AF1)&"|"&TRANSPOSE(Sheet1!A2&"|"&Sheet1!B2:AF2),"|"); SPLIT(TRANSPOSE(Sheet1!B1:AF1)&"|"&TRANSPOSE(Sheet1!A3&"|"&Sheet1!B3:AF3),"|") })

Another alternative is to add the formula every 366 rows. In order to avoid to have to edit the formula manually you could use INDIRECT, ROW and ROUNDDOWN

=ArrayFormula(SPLIT(TRANSPOSE(Sheet1!$B$1:$AF$1)&"|"&TRANSPOSE(INDIRECT("Sheet1!$A"&ROUNDDOWN((ROW()-2)/31)+2,TRUE)&"|"&INDIRECT("Sheet1!$B"&ROUNDDOWN((ROW()-2)/31)+2&":AF"&ROUNDDOWN((ROW()-2)/31)+2,TRUE)),"|"))

NOTE: Replace 31 by 365

=TRANSPOSE(Sheet1!$C$1:$Z$1) I can use transpose to get the dates/values, but it's fixed range. How can I make it dynamic to import dates as they are added?

If the number of dates isn't fixed, the formula complexity increases. To help us, the following named ranges should be created on an auxiliary sheet

Columns =COUNTIF(Sheet1!1:1,"<>")
DateHeaders ="Sheet1!R1C2:R1C"&Columns

The resulting formula is:

=ArrayFormula(SPLIT(TRANSPOSE(INDIRECT(DateHeaders,FALSE))&"|"&TRANSPOSE(Sheet1!A2&"|"&INDIRECT("Sheet1!R2C2:R2C"&Columns,FALSE)),"|"))

The above formula makes use of INDIRECT and R1C1 notation and it works only for the first "name" row.

As was mentioned previously, one way is to make an array with SPLIT and its arguments for each row.

=ArrayFormula({ SPLIT(TRANSPOSE(INDIRECT(DateHeaders,FALSE))&"|"&TRANSPOSE(Sheet1!A2&"|"&INDIRECT("Sheet1!R2C2:R2C"&Columns,FALSE)),"|"); SPLIT(TRANSPOSE(INDIRECT(DateHeaders,FALSE))&"|"&TRANSPOSE(Sheet1!A3&"|"&INDIRECT("Sheet1!R3C2:R3C"&Columns,FALSE)),"|") })

If we don't want to use an array, then we could repeat the following formula manually

First we should add an additional named range

DatesCount =Columns-1

=ArrayFormula(SPLIT(TRANSPOSE(INDIRECT(DateHeaders,FALSE))&"|"&TRANSPOSE(INDIRECT("Sheet1!$A"&ROUNDDOWN((ROW()-2)/DatesCount)+2,TRUE)&"|"&INDIRECT("Sheet1!R"&ROUNDDOWN((ROW()-2)/DatesCount)+2&"C2:R"&ROUNDDOWN((ROW()-2)/DatesCount)+2&"C"&Columns,FALSE)),"|"))

But the above formula implies to move or delete and add again the above formula to do every time that a date is added which could be very tedious and prone to error so it's very likely that the best approach is to use an array.

Another alternative is to use an script. Below are some links to Q&A about "unpivot data"

NOTE: The built in QUERY function relies on Google QUERY Language which doesn't include an UNPIVOT function.

Related Topic