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

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 2

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