How to transpose multiple rows to 1 column

I have a set of data 245 rows deep by 28 columns wide. I want to move each row of data into a single column where each row of data is then vertically stacked above the row below.
I can’t just use Copy,Paste,Transpose as it leaves me with a lot of manual work moving the 245 columns into one.

e.g.
from:

A B C D

1 2 3

4 5 6

7 8 9



to:

A

1

2

3

4

5

6

7

8

9



Any help on this would be much apprecicated!

Hi - You can also

  • Use a formula like: =OFFSET($A$1;INT((ROW()/3,1));ROW($A$1:$A$3)-1) (validation with Ctrl+Shift+Enter) that will create a 3-unit matrix (see E1: E3 in the RegrouperUneColonne.ods attached example),
  • Select E1:E3 and
  • Copy these three lines down through the fill handle by pressing the Ctrl key (see the result in example column F)

Note:

  • Column E is not necessary to the result. I have added that to decompose the solution and facilitate its understanding
  • Adapt the formula to the number of column. For example 4 columns:

=OFFSET($A$1;INT((ROW()/4,1));ROW($A$1:$A$4)-1)

[EDIT] New RegroupeUneColonne.ods with data in A to Z

Thankyou very much! I can’t seem to get it to work for all my data. I’ve never used Array formulas before and I’m having trouble… I’ve got data in A1:Z242. In column AA I add your adjusted formula =OFFSET($A$1,INT((ROW()/242)),ROW($A$1:$A$242)-1) , hit CTRL,Shift,Enter and it fills AA1:AA26, then Err:522 in AA27 and blank values after. I’ve tried selecting AA1:AA26, holding Ctrl and dragging down, but that just repeats the values in AA1:AA26…

If you data are in A1:Z242, use:

=OFFSET($A$1;INT((ROW()/26,1));ROW($A$1:$A$26)-1)

Cheers Pierre, thankyou very much for your help. I still couldn’t get your formula to work - I must be doing something wrong. Karolus’s answer has worked for me though, so problem is now solved. Once again thanks for your help!

Hallo

Insert new Column A , select your range > starting from B1 with shortcut ctrl+shift+→↓
and run :

from itertools import chain

def stack_selection_to_column(*_):
    doc = XSCRIPTCONTEXT.getDocument()
    sel = doc.CurrentSelection
    sheet = sel.Spreadsheet
    data = sel.DataArray
    data = tuple((entry,) for entry in chain.from_iterable(data))
    outrange = sheet.getCellRangeByPosition(0, 0, 0, len(data)-1)
    outrange.DataArray = data

Of course, its python, first you have to create a new file into path
$(your_LO_config)/Scripts/python/<some filename>.py
with content above.

Stack_to_one_column.ods

Thanks for the help but that causes Calc to crash. I don’t know how to find the exact problem…

works for me in 0.2 seconds for selection of 30Columns*300Rows

of course, do not select **whole** Columns

could I send you my spreadsheet for you to run the code on?

It seems you are faster if you adapt the Formula-solution from @PYS

I’ve attached an example-file with python-script in my first answer.

Thanks man! That’s worked! Really appreciate your help!