Stacking multiple columns on to one?

Question

I am using Google SpreadSheet, and I'm trying to have multiple sheets containg a list of words. On the final sheet, I would like to create a summative list, which is a combination of all the values in the column. I got it sort working using =CONCATENATE() , but it turned it into a string. Any way to keep it as a column list?

Here is an example as columns:

Sheet1

• apple
• orange
• banana

Sheet2

• pineapple
• strawberry
• peach

FinalSheet

• apple
• orange
• banana
• pineapple
• strawberry
• peach

Answer 1

Much more simple:

={sheetone!A2:A;sheettwo!A2:A}

Answer 2

Updated Answer

I was right there is a much better solution. It's been posted below but I'm copying it here so it's in the top answer:

=unique({A:A;B:B})

Caveat: This will include one blank cell in certain scenarios (such as if there's one at the end of the first list).

If you're not concerned with ordering and a tailing blank cell a simple sort() will clean things up:

=sort(unique({A:A;B:B}))

Otherwise a filter() can remove the blanks like so:

=filter(unique({A:A;B:B}),NOT(ISBLANK(unique({A:A;B:B}))))

The following is the old deprecated answer

I'm confident that this is "The Wrong Way To Do It", as this seems such an absurdly simple and common task that I feel I must be missing something as it should not require such an overwrought solution.

But this works:

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

If your data contains any ';' characters you'll naturally need to change the delimiter.

Answer 3

The basic way, is just to do it as arrays like so

={A1:A10;B1:B10...etc}

The problem with this method, as I found out is that its very time consuming if you have lots of columns. I've done some searching around and have come across this article:

Joining Multiple Columns Into One Sorted Column in Google Spreadsheets

The core formula is

=transpose(split(arrayformula(concatenate(if(len(A:Z)>0,A:Z&";",""))),";"))

Obviously you'd replace the A:Z to whatever range you want to use. And if you want to do some sorting or removing duplicates, you'd simply wrap the the above formula in a SORT() and/or UNIQUE() method, like so..

=sort(unique(transpose(split(arrayformula(concatenate(if(len(A:Z)>0,A:Z&";",""))),";"))))

Hope this helps. Happy coding everyone :)

Answer 4

=TRANSPOSE(SPLIT(TEXTJOIN("@",TRUE,TRANSPOSE(A:C),TRANSPOSE(D1:D5)),"@",FALSE,FALSE))

• use a preferred delimiter absent in the data (instead of @) if needed
• the first 1 (TRUE) parameter means IGNORE EMPTY, which is very important in this case..
• the A:C and D1:D5 are the ranges to combine
• all values remain there - not using UNIQUE

Answer 5

You can use this:

=unique({A1:A;B1:B})

Works perfect here!

Answer 6

The unique() function gets rid of blank spaces, but wasn't helpful for me because some of my rows repeat. Instead I first filter the columns by len() to remove blank cells. Then I combine the columns together in the same way.

={filter(A:A, len(A:A)); filter(B:B, len(B:B))}