Ask Your Question

Convert 2D table to single colum

asked 2017-07-04 08:48:46 +0100

pschonmann gravatar image

Hi i have 2D Table

X1 X2 X3 X4

Y1 Y2 Y3 Y4

Z1 Z2 Z3 Z4

I want output










How to do that without copy & paste colums. Is there any handy tool ?

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2017-07-04 09:32:22 +0100

JohnSUN gravatar image

updated 2017-07-04 10:22:52 +0100

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

edit flag offensive delete link more


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

pschonmann gravatar imagepschonmann ( 2017-07-04 10:12:02 +0100 )edit

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

JohnSUN gravatar imageJohnSUN ( 2017-07-04 10:25:26 +0100 )edit

answered 2017-07-04 11:18:40 +0100

updated 2017-07-04 11:21:26 +0100

Let's say you want the resulting column to start from first row (e.g., from F1).

Then use following formula in F1:


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

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-07-04 08:48:46 +0100

Seen: 72 times

Last updated: Jul 04 '17