2

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

player0
  • 124,011
  • 12
  • 67
  • 124
Siesta
  • 451
  • 7
  • 21

1 Answers1

2

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

player0
  • 124,011
  • 12
  • 67
  • 124
  • It only works if iterative calculations is On, and then it recalculates on every click it seems – Siesta Nov 11 '21 at 13:34
  • @Siesta nah, you are using it wrong way... use it in other sheet not in range A4:L of current (input) range. if you want it to use it in input sheet either restrict ranges in formula or use it from column M onwards – player0 Nov 11 '21 at 13:39
  • 1
    Oh, your right. When added in the other sheet it worked! – Siesta Nov 11 '21 at 13:47
  • 1
    If @player0's answer was successful, you could mark it as accepted. – Emel Nov 11 '21 at 14:51