Search code examples
google-sheetstransposeunpivotflattentextjoin

going from wide to long with empty cells in google sheets

I am trying to go from wide to long for two columns with blanks retaining information in the last two columns.

enter image description here

Please see google sheets link for example and intended effect:

Solution

  • use:

    =INDEX(QUERY(SPLIT(FLATTEN(
 IF(A2:B5="",,A2:B5&"×"&C2:C5&"×"&D2:D5)), "×"), 
 "where Col2 is not null", ))