# Convert 2D table to single column

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:

1. Ctrl+* (select full data range)
2. Ctrl+C (or Ctrl+Ins - copy data to
clipboard)
3. Ctrl+End and twice Right arrow (or just mouse click - move cursor to empty cell)
Special), T (choose Transpose),
5. Ctrl+X (cut transposed table)
6. Alt+F Enter Enter (create new empty Text
Document)
7. Ctrl+Shift+V F Enter
(paste as Formatted Text RTF)
8. Twice
Up arrow (move cursor to any cell of
table)
9. Alt+A C A P Enter (choose
Text
Paragraph)
10. Ctrl+A (or Ctrl+Shift+End - select all)
11. Ctrl+C (copy)
13. Choose target cell
14. Press Ctrl+V

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