google sheets – Help Please? Pivot Table doesn’t seem to be correct way to fix? Add rows based on data type and return value?

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 value is null.