UNPIVOT function in Google Sheets

Question

Background: Have a sheet that is being constantly updated with new rows, where the rows are in a PIVOTED fashion. Think left column is date, following columns are staff-ids - extending to right as new staff joins.

I have researched a bit - but did not find my required solution. Eg: the QUERY function has PIVOT but NO UNPIVOT function. So ...

Is there a FORMULA based method (without scripting) that can produce the normalized/UNPIVOTed data from the already pivoted data?

I'm not looking for a solution, hence no sample sheet. But rather, if this is possible, please point me to the functions that could do it ?

Answer 1

I don't know a formula that does this directly. What I would do is: 1) Use COUNT() twice, to count the columns and rows that contain data. Let's call these C and R. 2) Use simple formulas to write all combinations of C and R into C*R rows and 2 columns. Use more rows to allow for C and R to grow. 3) Use OFFSET() to read the pivoted data into the 3rd (unpivoted) column. 4) If you need to get rid of empty cells, use QUERY() on the 3 unpivoted columns. Hope that helps.