Hi i have 2D Table
X1 X2 X3 X4
Y1 Y2 Y3 Y4
Z1 Z2 Z3 Z4
I want output
X1
Y1
Z1
X2
Y2
Z2
X3
Y3
Z3
How to do that without copy & paste colums. Is there any handy tool ?
Hi i have 2D Table
X1 X2 X3 X4
Y1 Y2 Y3 Y4
Z1 Z2 Z3 Z4
I want output
X1
Y1
Z1
X2
Y2
Z2
X3
Y3
Z3
How to do that without copy & paste colums. Is there any handy tool ?
If you ready use “a dirty hack” so do this:
This sequence should not take you more than 30 seconds - it’s a little faster than reinvent the formula to convert the data, or write macros
Works fine, but its too long. Expected internal function in CALC, anyway, thanks.
It’s too long… Of course, you are fully right In this case try correctly edit addresses in formula like as
=IF(ROW()>COUNTA($A$1:$D$3);"";OFFSET($A$1;MOD((ROW()-1);ROWS($A$1:$D$3));INT((ROW()-1)/ROWS($A$1:$D$3))))
Let’s say you want the resulting column to start from first row (e.g., from F1
).
Then use following formula in F1
:
=INDEX($A$1:$D$3;MOD((ROW()-1);ROWS($A$1:$D$3))+1;INT((ROW()-1)/ROWS($A$1:$D$3))+1)
and drag-copy it downwards as required.
Of course, using range name instead of $A$1:$D$3
is preferable.
EDIT: sorry, @JohnSUN is better, as usual