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

Question

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.

Answer 1

Here's another solution:

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

Answer 2

You may try:

=reduce(tocol(,1),B2:index(D:D,match(,0/(A:A<>""))),lambda(a,c,vstack(a,hstack(index(A:A,row(c)),index(1:1,column(c)),c))))