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

First time here? Check out the FAQ!

Calc how to transfer column from vertical to H

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

add a comment

0

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

1

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)))}`

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?

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.

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

Seen: **180 times**

Last updated: **Jun 10 '19**

Can I use Microsoft Publisher files with LibreOffice? [closed]

Please refine "Search" in Calc - implement functions in Gnumeric [closed]

Is there a LibreOffice .odt, .ods viewer for Android? [closed]

How do Ubuntu LibreOffice builds differ from vanilla LibreOffice builds? [closed]

Spell check only works for admin account windows not network user accounts [closed]

How do you add bullets to an already number-bulleted group of statements? [closed]

Suppress Java errors when installing from an msi [closed]

How do I get document information from the command line? [closed]

Alternating table column numbering, Col1 Numbers, Col2 Letters? [closed]

Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.