google-sheetsgoogle-apps-scriptgoogle-sheets-formulaunpivot

Google Sheets : Need help in automating the transpose option from columns to rows


I am looking for an option in Google sheets where I can automate pivot/ transpose the values in columns to rows. something similar to power pivot in excel.

I have shared a sample and my requirement in the below google sheet link.

https://docs.google.com/spreadsheets/d/e/2PACX-1vQk5CGiXwkJcUT63ghfx77QIw2ojXMkXJJHYMCAVKfjNu_GnVroOEQD-g_SGoZNVw0pvH6_YBFzdMGs/pubhtml

Appreciate your help here.

enter image description here


Solution

  • Here's another solution:

    =ARRAYFORMULA(QUERY(SPLIT(TOCOL(A2:A&"|"&B1:D1&"|"&B2:D),"|"),"where Col2<>''"))
    

    enter image description here