Transforming data from multiple columns to rows and keeping "labels"

user_4727118 0
Viewed 33

I am trying to create a Google Sheet where some of the data is inputet on a column basis. Like this

enter image description here

This input is done in a Input sheet. And then I want to transform the data into this form in the Output sheet.

enter image description here

I have no idea how I could do this in a simple way. I did think about some solution using the Transpose function, but I can't realy wrap my head around how to solve this.

Here is a link to the sheet if anyone can help me out: https://docs.google.com/spreadsheets/d/1W244e2IOyORYyMPg6VnRiYDgX2ux7VZn4N27mEUSOII/edit#gid=1942499075

google-query-language flatten google-sheets if-statement split
1 Answers

try:

=INDEX(QUERY(SPLIT(FLATTEN(IF('Input sheet'!C4:L="",,
 'Input sheet'!A4:A&"×"&'Input sheet'!B4:B&"×"&'Input sheet'!C3:L3&"×"&'Input sheet'!C4:L)), "×"), 
 "where Col2 is not null"))

enter image description here

user_56326290
Related