I am struggling with either using a pivot table or writing a query that will get the database in Columns A-E to format as Columns G-I. As you can imagine, I'm working with hundreds of thousands lines of data; however, the Column Helpers in A-E will also be set to what currently exists.


1 Answer 1


I've added a new sheet ("Erik Help") with two options for you. However, here I will only post the option that matches your exact request. In B1:

=ArrayFormula({"ID","Type","Value";QUERY(SPLIT(FLATTEN(FILTER(Sheet1!A2:A,Sheet1!A2:A<>"")&"|"&Sheet1!B1:E1&"|"&FILTER(Sheet1!B2:E,Sheet1!A2:A<>"")),"|"),"Select * Where Col3 Is Not Null")})

This formula creates the three headers (which you can change as you like), and then does the following:

1.) concatenate all non-blank entries in A2:A, a pipe symbol, each header in B1:E1, another pipe symbol and each element of the B2:E grid where A2:A is not blank. This will form a grid of results the same size as B2:E where A2:A is not blank, each in the format X|Y|Z.

2.) this grid of results is FLATTENed into one column.

3.) that column of results is split into three columns at the pipe symbols.

4.) QUERY weeds out any entries where the third column ("Value") is null.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.