Ask Your Question

Calc how to transfer column from vertical to H

asked 2019-06-10 19:26:45 +0200

evx80 gravatar image

i have two separate columns i need to put A1 horizontal following B1 next horizontal then A2 etc.... kindly see the pic below.image description

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2019-06-10 19:56:59 +0200

Opaque gravatar image


this solution is only worth to be used if you have a lot of rows in column A and B

Into cell D1 set formula: =INDIRECT("A" & INT((COLUMN(D1)-3)/2)+1)
Into cell E1 set formula: =INDIRECT("B" & INT((COLUMN(E1)-4)/2)+1)
Select range D1:E1 and drag to column F (or wahever fits to the numbers of values in columns A and B)

Note: Please be aware that LO supports 1024 columns (only).

edit flag offensive delete link more


works like magic thanks lot really helps.

evx80 gravatar imageevx80 ( 2019-06-10 20:24:45 +0200 )edit

answered 2019-06-10 21:14:35 +0200

Lupp gravatar image

updated 2019-06-10 22:35:57 +0200

Only for fans of tricky solutions:
entered into D1 with Ctrl+Shift+Enter for array-evaluation to process the rtange A1:B11 with its 22 elements e.g.

===Edit1 2019-06-10 20:35 UTC===

Concerning the comment by @SM_Riga: I tested the tricky solution.
Sketched in LibreOffice V, then saved and tried with V and with Apache OpenOffice V 4.1.5. See this rectified demo. (Here the original demo.)

Rectified formula: {=TRANSPOSE(OFFSET($A$1;INT((ROW($A$1:$A$22)-1)/2);ISODD(ROW($A$1:$A$22)-1)))}

edit flag offensive delete link more


Nice! I see the idea, but unfortunately for me this returns #VALUE when entered as an array function. The OFFSET part returns correct array {A1, B1, A2, B2, ..., A11, B11}, but nested inside TRANSPOSE gives #VALUE Entering TRANSPOSE as standalone function in an other cell and passing the array from OFFSET there returns expected 'horizontal' range. Just curious, what can be the cause?

SM_Riga gravatar imageSM_Riga ( 2019-06-10 21:55:05 +0200 )edit

Yes. The formula contained a flaw:
The second parameter of the OFFSET() should read INT((ROW($A$1:$A$22)-1)/2). Otherwise the INT() would only work on a subexpression being integer anyway. In the current case this didn't cause errors for me due to the ways of automatic conversion. However, I don't want to rely on such details.

Lupp gravatar imageLupp ( 2019-06-10 22:31:37 +0200 )edit

Great ....

Opaque gravatar imageOpaque ( 2019-06-11 00:43:52 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-06-10 19:26:45 +0200

Seen: 180 times

Last updated: Jun 10 '19