# Calc how to transfer column from vertical to H

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

edit retag close merge delete

Sort by » oldest newest most voted

Hello,

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

more

works like magic thanks lot really helps.

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

Only for fans of tricky solutions:
{=TRANSPOSE(OFFSET($A$1;INT(ROW($A$1:$A$22)-1)/2;ISODD(ROW($A$1:$A$22)-1)))}
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 6.3.0.0.beta1, then saved and tried with V 6.2.3.2 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)))}

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?

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

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

Great ....

( 2019-06-11 00:43:52 +0200 )edit