0

I have a small program created to set matrix values after pre-determined patterns assigned to me. The matrix values are printed column by column to a text file (as per instructions given to me). For example, each row in the .txt file sample below would be a column of information:

1,2,3,4,5 
4,5,3,5,6
4,5,1,6,9

I wish to import this file to Google Sheets in its actual matrix format so that I can look at the patterns:

1 4 4
2 5 5
3 3 1
4 5 6 
5 6 9

I have found google sheets will automatically put this data into rows for me, and then I have found information here about the Transpose function that I can impose manually to change those rows to columns. These matrices will be many and arbitrarily large. Is there a way to automate the transpose?

  • Did you already considered the use of open ended references, the use of add-ons or scripts? What is the scope of the automation? Are the matrices already on the spreadsheet? How the process be triggered? – Rubén Oct 25 '16 at 15:44
  • @Rubén I'm unfamiliar with any add-ons or how to script. As far as the automation goes, there is no requirement except that I want to make it easier on myself. This is a math homework assignment for grad school. I'm programming in Java, where my skills are only rudimentary, and writing the matrices to text files in the comma delimited form posed in my question. I am then uploading the text files using the file>import option in Sheets. Fairly cumbersome, but the only way that I know. I'm totally open to options that are easy for a layman to understand. I hope I answered all your questions! – rocksNwaves Oct 25 '16 at 16:41
  • Please update your question to include the additional details and make your question more specific. For further details see How to Ask. – Rubén Oct 25 '16 at 17:23
  • @Rubén You mean you need to know the programming language that is sourcing the text files? Or you want me to add that I am clicking "file>import" and hitting the upload button to get my textfile to Sheets? Or you didn't notice that the scope of automation desired was explicitly stated in the question: "Automatically transpose imported txt file rows to columns in Google Sheets". I want to automatically transpose columns from rows, from a text file. I said that. Thanks for your input. – rocksNwaves Oct 25 '16 at 17:43
2

One way to make it really automatic would be to stick the CSV someplace online. Then you could do something like . . .

=TRANSPOSE(IMPORTDATA("http://woodwardtw.github.io/test/comma.csv"))

That way any time you changed the CSV source file the SS would change with it.

Alternately, I believe making a new sheet and simply setting a wide cell pattern referencing your raw data sheet will get you there. Putting the following on Sheet 2 flips the data from Sheet 1 into columns. You might need to set your end cell to something more aggressive outer bound. Example here.

=transpose(Sheet1!A1:Z20)

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.