Stack multiple columns into one

Question

I want to do a simple task but somehow I'm unable to do it. Assume that I have one column like:

a
z
e
r
t

How can I create a new column with the same value twice with the following result:

a
a
z
z
e
e
r
r
t
t

I've already tried to double my column and do something like :

=TRANSPOSE(SPLIT(JOIN(";",A:A,B:B),";"))

but it creates:

a
z
e
r
t
a
z
e
r
t

I get inspired by this answer so far.

how about copying the data, pasting it twice to another column and then sorting? — pawelty, Aug 05 '16 at 09:27

score 4 · Accepted Answer · edited Apr 13 '17 at 12:57

4

Try this:

=SORT({A1:A5;A1:A5})

Here we use:

sort
{} to combine data

Accounting your comment, then you may use this formula:

=QUERY(SORT(ArrayFormula({row(A1:A5),A1:A5;row(A1:A5),A1:A5})),"select Col2")

The idea is to use additional column of data with number of row, then sort by row, then query to get only values.

And join→split method will do the same: =TRANSPOSE(SPLIT(JOIN(",",ARRAYFORMULA(CONCAT(A1:A5&",",A1:A5))),","))

Here we use range only two times, so this is easier to use. Also see Concat + ArrayFormula sample.

edited Apr 13 '17 at 12:57

Community

1
1

answered Aug 05 '16 at 09:46

Max Makhrov

17,309
5
55
81

Thanks but I don't want it to be sorted it can be 3;3;1;1;2;2;4;4;5;5 for example – ZazOufUmI Aug 05 '16 at 09:49
Awesome, works perfectly! Didn't thought it would be such a complicated formula for this kind of task. Thanks!!! Saved me a lot of copy/paste – ZazOufUmI Aug 05 '16 at 10:00
Thanks. There's another way to accomplish this. I've made one more update. – Max Makhrov Aug 05 '16 at 10:02

score 1 · Answer 2 · answered Aug 05 '16 at 10:55

1

Few hundreds rows is nothing :)

I created index from 1 to n, then pasted it twice and sorted by index. But it's obviously fancier to do it with a formula :)

answered Aug 05 '16 at 10:55

pawelty

1,000
8
27

score 1 · Answer 3 · answered Aug 05 '16 at 12:13

Assuming Your list is in column A and (for now) the times of repeat are in C1 (can be changed to a number in the formula), then something simple like this will do (starting in B1):

=INDEX(A:A,(INT(ROW()-1)/$C$1)+1)

Simply copy down as you need it (will give just 0 after the last item). No sorting. No array. No sheets/excel problems. No heavy calculations.

Stack multiple columns into one

3 Answers3