We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Convert 2D table to single column [closed]

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

pschonmann gravatar image

updated 2020-10-09 11:26:01 +0200

Alex Kemp 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 reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-07-30 00:30:34.893987

2 Answers

Sort by » oldest newest most voted

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

JohnSUN gravatar image

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

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 +0200 )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 +0200 )edit

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

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

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

Question Tools

1 follower


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

Seen: 628 times

Last updated: Oct 09 '20