Stacking multiple columns on to one?

abritez picture abritez · Feb 25, 2012 · Viewed 76.5k times · Source

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

Lake picture Lake · Jan 4, 2013

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.