hi
i have two separate columns i need to put A1 horizontal following B1 next horizontal then A2 etc… kindly see the pic below.
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.
Great …
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).
works like magic thanks lot really helps.