Assign column header to each data in the table and create a table with two column (Google Sheet)

0
votes

I have a set of timestamped data in a number of columns (column headers are named with time durations). I want to assign this column header to each data inside the table and create a table with two columns with "time" and "data" as new headers. (SpreadSheet link: https://docs.google.com/spreadsheets/d/1SxTZhPjDtl50am6kGdjgQexmfgp_o4wUp3ZZpNfFVFY/edit?usp=sharing ) enter image description here

1
google-sheetsspreadsheet

1 Answers

2
votes

Give this a try

=ArrayFormula(query(iferror(split(trim(transpose(split(textjoin(char(10), 1, query(if(A2:DP8<>"", 1:1&"_"&A2:DP8&char(10),),,rows(A1:A8))),char(10)))),"_")), "where Col1 <>''"))

and see if it works?