I need help to convert a vertical line of data in a row to columns. It involves about
60 items with 14 rows each, to 14 columns with 60 rows. which change daily. I will label and format it once the problem is solved. This is a basic copy/paste effort. I can export prices ok, but the feature to export the remaining data isn’t available, evidently. This is a transfer I make periodically. First image is simply the raw paste-in on a Libre calc sheet of the second one. Thanks in advance, Don
Copy the data and use Menu/edit/paste-special - Transpose.
2 Likes
youre both right @Hrbrgr @mariosv … but @mrow22 asks for transposing parts of a single column of 840 rows into a »Table« of 60rows with each 14 Columns.
2 Likes
marisov seems to have solved it, for this particular copy/paste effort from this particular site. I will still do the separate .csv export with the closing prices only on a day-to day basis. Works well, everything goes where it all should, have used the technique for 25 years.
So With this transposition I should be able to land data in the same cells from month to month as I’ve done in the past when the “paste” effort would do it before, without any conversion. I should be able to label the columns and then manually paste the resulting block of data into the Report tab along with the price info, without going through a lengthy impractical procedure, cell by cell (not ever likely). Alternately, just keep it on a separate tab. Image of first result here I have done the column widths and headings to check if it continually lands in same cells and appropriate columns. The initial single A3 column paste is not needed at all.:
My version is 7.3.72 Lately Office Libre getting very slow, not sure why. SSD has plenty of space, but PC is a 11 yr old HP Pavilion laptop…
Yes, it’s a lousy way to invest but too late to change it now. Old dogs, new tricks, etc etc. A little bit here, little bit there. Most were successful, a few crooks, losers and dogs were chosen. A few simply went pfffft!
— Thanks all, am still interested in any additional feedback. ~Don
To transpose slices of 14 rows from a single Column you may use the initial Formula
=TRANSPOSE(OFFSET($A$2;14*(ROW(A1)-1);0;14;1))
write for example into C2, enter, and while holding ctrlkey pull down from P2 to P62
see attachment.
transpose_slices_of_14.ods (15.0 KB)
2 Likes
Thanks, I will try. Ok, tried it, nothing happens. Most likely not doing this correctly.
But let me redo my images first, maybe another solution might emerge. The formula
may not be easy for me to use. I am not sure which location and exact instructions to drop it. Maybe this will help.
I was wrong about the rows, the total number of rows is around 950 when doing this extra site-page row-only copy/paste. I have re-done my images to show more clearly what I am writing about. I was hoping to be able to select the vertical row of data from this “paste” in my sheet and flip it to legible columns which I then would read into a different tab for a neat presentation. Not a lot of time spent doing the op. Thanks to everyone for trying to help. ~D
I think I will need a step-by-step using the formula in terms I can understand. I got this far with my
spreadsheet, but self-taught and many holes in my knowledge as I am not creating any longer. – Thanks -
Formula I just tried: =TRANSPOSE(OFFSET($A$3;16*(ROW(A1)-1);0;16;1))
From your last DIRECT DATA TRANSFER screenshot… you need slices of 16 starting on $A$3
=TRANSPOSE(OFFSET($A$3;16*(ROW(A1)-1);0;16;1))
I think I will need a step-by-step using the formula in terms I can understand. I got this far with my
spreadsheet, but self-taught and many holes in my knowledge as I am not creating any longer. – Thanks -
Formula I just tried: =TRANSPOSE(OFFSET($A$3;16*(ROW(A1)-1);0;16;1))
I think I am understanding I’d need to do this conversion using 16 rows at a time. That might prove to be impractical.
– thks
Follow up, will try marisov’s simple recommendation and see if it works out on a regular basis, on a pre-formatted similar to the rough one I posted above. Seems to work, if so then problem solved. Thanks to all for this help and intelligent responses. Cheers, Don