Ask Your Question
0

How to transpose multiple rows to 1 column

asked 2015-03-24 09:54:52 +0200

bananas gravatar image

updated 2015-03-24 09:57:21 +0200

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!

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2015-03-24 11:29:58 +0200

karolus gravatar image

updated 2015-03-24 15:47:48 +0200

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

edit flag offensive delete link more

Comments

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

bananas gravatar imagebananas ( 2015-03-24 13:35:32 +0200 )edit

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

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

karolus gravatar imagekarolus ( 2015-03-24 14:38:53 +0200 )edit

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

bananas gravatar imagebananas ( 2015-03-24 14:40:32 +0200 )edit

It seems you are faster if you adapt the Formula-solution from @pierre-yves samyn

karolus gravatar imagekarolus ( 2015-03-24 15:33:01 +0200 )edit

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

karolus gravatar imagekarolus ( 2015-03-24 15:51:24 +0200 )edit

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

bananas gravatar imagebananas ( 2015-03-24 16:12:39 +0200 )edit
1

answered 2015-03-24 14:57:53 +0200

pierre-yves samyn gravatar image

updated 2015-03-24 16:12:19 +0200

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](/upfiles/14272098791874465.ods) with data in A to Z

edit flag offensive delete link more

Comments

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...

bananas gravatar imagebananas ( 2015-03-24 15:56:41 +0200 )edit

If you data are in A1:Z242, use:

=OFFSET($A$1;INT((ROW()/26,1));ROW($A$1:$A$26)-1)
pierre-yves samyn gravatar imagepierre-yves samyn ( 2015-03-24 16:10:24 +0200 )edit

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!

bananas gravatar imagebananas ( 2015-03-24 16:15:13 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-03-24 09:54:52 +0200

Seen: 2,894 times

Last updated: Mar 24 '15