Stacking multiple columns on to one?
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
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.
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 :)
You can use this:
=unique({A1:A;B1:B})
Works perfect here!
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))}