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

# Convert 2D table to single column [closed]

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 ?

edit retag 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

Sort by » oldest newest most voted

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

more

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

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

( 2017-07-04 10:25:26 +0200 )edit

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

more