0

Column A of my Google Spreadsheet contains dates, columns B to G contain numeric values, line 1 contains the header names. Due to automatic updates the line count varies, but the column count is static.

enter image description here

Is there a way to change the format of my tabel to:

enter image description here

I want every pair of A + (B to G) in a new table below the each other. In the new table column A should contain the dates, B the header name of the source table column, C and D empty and the values of the source table column in column E.

The new table should update automatically after the source table gets updated. Since the line count varies with every update, I unfortunately have no clue and any help is much appreciated.


SOLUTION:

Thank you very much for your help, highly appreciated. I made one change to marikamitsos solution to work with any number of rows in the source table:

={ArrayFormula(SPLIT(INDIRECT("SourceData!A2:A"&$K$2)&"@"&SourceData!$B$1&"@@@"&INDIRECT("SourceData!B2:B"&$K$2);"@";1;0)); 
ArrayFormula(SPLIT(INDIRECT("SourceData!A2:A"&$K$2)&"@"&SourceData!$C$1&"@@@"&INDIRECT("SourceData!C2:C"&$K$2);"@";1;0));
ArrayFormula(SPLIT(INDIRECT("SourceData!A2:A"&$K$2)&"@"&SourceData!$D$1&"@@@"&INDIRECT("SourceData!D2:D"&$K$2);"@";1;0));
ArrayFormula(SPLIT(INDIRECT("SourceData!A2:A"&$K$2)&"@"&SourceData!$E$1&"@@@"&INDIRECT("SourceData!E2:E"&$K$2);"@";1;0));
ArrayFormula(SPLIT(INDIRECT("SourceData!A2:A"&$K$2)&"@"&SourceData!$F$1&"@@@"&INDIRECT("SourceData!F2:F"&$K$2);"@";1;0));
ArrayFormula(SPLIT(INDIRECT("SourceData!A2:A"&$K$2)&"@"&SourceData!$G$1&"@@@"&INDIRECT("SourceData!F2:G"&$K$2);"@";1;0)) }

In $K$2 i put:

=ROWS(FILTER(SourceData!A:A; NOT(ISBLANK(SourceData!A:A)))
0

Please use the following formula

={ArrayFormula(SPLIT( A2:A8&"@"&$B$1&"@@@"&B2:B8,"@",1,0)); 
  ArrayFormula(SPLIT( A2:A8&"@"&$C$1&"@@@"&C2:C8,"@",1,0));
  ArrayFormula(SPLIT( A2:A8&"@"&$D$1&"@@@"&D2:D8,"@",1,0));
  ArrayFormula(SPLIT( A2:A8&"@"&$E$1&"@@@"&E2:E8,"@",1,0));
  ArrayFormula(SPLIT( A2:A8&"@"&$F$1&"@@@"&F2:F8,"@",1,0));
  ArrayFormula(SPLIT( A2:A8&"@"&$G$1&"@@@"&G2:G8,"@",1,0)) }

enter image description here

How the formula works:

We use the ampersand & to concatenate the cells in each row as well as the header of the columns.
We also use a non-common character like @ in this case and SPLIT to next separate them again

ArrayFormula(SPLIT( A2:A8&"@"&$B$1&"@@@"&B2:B8,"@",1,0))

We use @@@ to intercept the formula and create the empty columns.
We repeat the above for each column and finally stack all the created arrayformulas using the semicolon ;.

Functions used:

| improve this answer | |
  • Welcome. Please remember that as per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well. – marikamitsos Jul 24 at 18:48
  • Thank you @marikamitsos, works like a charm! As suggested by Erik Tyler I separated source data and the generated output. I also altered your solution to work with any number of rows and now it results in exactly the output I desired. Once again, thank you very much and also your great explanation. – Anton Jul 27 at 13:10
  • Glad it worked for you. Also thank you for taking the time to accept. – marikamitsos Jul 27 at 13:22
0

Anton, it is difficult to write formulas without access to your actual data. Testing anything would require that the volunteers here spend their time to recreate your example data set themselves. The best way to get fast, accurate help is to share a link to your sample data sheet, being sure to set Share permission to "Anyone with the link can edit."

That said, see if this produces the result you're after:

=ArrayFormula(SORT(SPLIT(FLATTEN(FILTER(A2:A,A2:A<>"")&"\"&B1:D1&"\\\"&FILTER(B2:D,A2:A<>"")&"\"),"\",1,0),2,1))

I'm not sure why you want the blank columns, however. If you are wanting to add data to those columns, you won't be able to with just the above formula. In that case, the safest way to assure the data stays together and yet gives you the two empty columns would be to perform a QUERY on the above formula:

1.) In Column A:

=ArrayFormula(QUERY(SORT(SPLIT(FLATTEN(FILTER(A2:A,A2:A<>"")&"\"&B1:D1&"\"&FILTER(B2:D,A2:A<>"")&"\"),"\",1,0),2,1),"Select Col1, Col2"))

2.) Same row, in Column E:

=ArrayFormula(QUERY(SORT(SPLIT(FLATTEN(FILTER(A2:A,A2:A<>"")&"\"&B1:D1&"\"&FILTER(B2:D,A2:A<>"")&"\"),"\",1,0),2,1),"Select Col3"))

It also occurs to me that you should probably be putting these formulas in their own sheet, and not underneath the first data set in the original sheet, since the original data may expand (and would require modifications to the formula above to limit the ranges).

I recommend that you place the formulas in a separate sheet. In this case, you will need to add the original sheet name into every range reference in whichever formula(s) you use (e.g., A2:A becomes Sheet1!A2:A, etc., or whatever your actual sheet name is).

Do understand that FLATTEN is a not-yet-official Google Sheets function. As such, using it comes with the risk that Google may — or may not — choose to add it to the official functionality.

If this does not work for your purposes, please do share an link to your editable sample sheet.

| improve this answer | |
  • Thanky you Erik Tyler. I'll definitely provide a sample sheet next time I ask a question. Ultimately I went with marikamitsos' solution because it's only one formula. But I incorporated your suggestion to separate source data and the output. Once again, thank you very much for your help and your solution you provided! – Anton Jul 27 at 13:15
  • Glad it helped. – Erik Tyler Jul 27 at 16:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.