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)
  4. Ctrl+Shift+V (menu Edit-Paste
    Special), T (choose Transpose),
    Enter (paste and close menu)
  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
    menu Table-Convert-Table to
    Text
    Paragraph)
  10. Ctrl+A (or Ctrl+Shift+End - select all)
  11. Ctrl+C (copy)
  12. Alt+Tab (return to Calc)
  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

Convert Table To Single Column

Works fine, but its too long. Expected internal function in CALC, anyway, thanks.

It’s too long… Of course, you are fully right :slight_smile: 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 :wink: