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

Sounds like a question for web applications instead of stackoverflow — Micky McQuade, Feb 25 '12 at 07:14
Forgive me if i am incorrect, but I would need to write a spreadsheet function to get this to work. I would imagine this would be the place for this type of question. — abritez, Feb 25 '12 at 07:22
At this time, you could use embedded arrays. See http://stackoverflow.com/questions/15985637/appending-google-spreadsheet-arrays/15998230#15998230 — Rubén, May 15 '15 at 03:33
I'm slightly confused by something. No where in the question does it say this is looking for *unique* values yet the top answers assume it does. The way it is worded implies that duplicate values should be kept. The question is asking for *'stacking'* columns in a way similar to `CONCATENATE`. If the question is asking for a unique result then this should be made clear in the title and text of the question so people don't visit the page when trying to answer a different problem. — AnnanFay, Feb 02 '19 at 20:30
I wanted to add this as a comment to The Fool's answer, but I don't have enough reputation. "FLATTEN" is now documented. https://support.google.com/docs/answer/10307761 Cheers! — deadneck, Mar 08 '21 at 18:36

Lake · Answer 1 · 2015-12-14T02:37:17.417

84

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.

edited Dec 14 '15 at 02:37

answered Jan 04 '13 at 04:16

Lake

1,135
8
13

3

Thanks! It spent a lot of time until I found this! – Erika Mar 05 '13 at 08:15
1

It works but there are much simple ways to achieve the desired results. See http://stackoverflow.com/a/30039238/1595451 – Rubén May 15 '15 at 03:24
3

When I try unique, it puts the data into separate columns instead of merging them in any way, which completely defeats the point. The "deprecated" answer worked though :/ – Mooing Duck Aug 03 '15 at 03:36
Thanks! It works for me. Merge two columns in Google sheets. Happy Thanksgiving. – Hua Zhang Nov 26 '15 at 07:07
Why bother using unique() at all? The question doesn't say anything about removing duplicates. It looks like you can just remove it. – grayob Jun 15 '16 at 01:23
The "better" solution doesn't work in Google Sheets, as it does not produce unique results. The "Deprecated" approach however, works perfectly! – JVC Aug 25 '16 at 03:32
3

Deprecated solution works. The "better" solution produces a unique _combination_ of values. That is, it finds unique pairs in columns A and B at the same time. The deprecated method instead is joining the columns together, then finding the unique elements without regard to pairings. The "better" answer might be working for some people when the columns are not adjacent, such as on different sheets, etc. – Dannid Oct 19 '16 at 18:30
The "better" solution worked for me as long as I included each column independently, separated with the semi-colon. I didn't notice that at first. – Phlucious Dec 07 '17 at 20:26
Three additional notes. 1. trim() is also handy to remove whitespace 2. sort/unique over a multi-column range e.g. =sort(unique(trim(A2:B143))) in L2 3. after sorting/unique into columns you can show the count of each unique item using countifs() like this: =COUNTIFS($A$1:$A$800, $L$1:$L$800,$B$1:$B$800, $M$1:$M$800) – gaoithe Jan 10 '20 at 13:45
nice. is there an excel equivalent for this formula? it doesn't like ; – Christopher Turnbull Jun 19 '20 at 11:03
Better solution is below: `flatten(A:B)`. This will allow you to input a range of columns without listing each in `{A:A;B:B}`. – Vytas Bradunas Nov 30 '22 at 07:58

score 14 · Answer 2 · answered May 17 '16 at 22:20

14

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

answered May 17 '16 at 22:20

PowerAktar

2,341
1
21
17

3

It should be noted that this concatenates all values with a ; into one big string, and then splits them by a ; again to create the final array (=column). This will break (subtly) if any of the cells contain a ; themselves. If that's the case, change the two occurrences of ; in this snippet to a different character, e.g. § or * or anything that doesn't exist in your data. If you don't, what will happen is a cell like "a;b" becomes two separate cells in the final, big column; "a" and "b". This can cause delicate bugs :) – hraban Feb 01 '18 at 22:04
This doesn't deal with an array on two sheets. If you have the range in once piece you can save yourself the whole function there and call flatten. With you snippet you had to type out all ranges even twice. https://infoinspired.com/google-docs/spreadsheet/flatten-function-in-google-sheets/ – The Fool Sep 28 '20 at 02:07

score 10 · Answer 3 · edited May 04 '15 at 20:23

10

You can use this:

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

Works perfect here!

edited May 04 '15 at 20:23

Undo

25,519
37
106
129

answered May 04 '15 at 20:20

Diego Contezini

109
1
3

This seems to give an error if the given column ranges are not the same shape. – Dobes Vandermeer Aug 21 '19 at 19:15
is there an excel equivalent for this? it doesn't accept ; – Christopher Turnbull Jun 19 '20 at 11:03

grayob · Answer 4 · 2016-06-15T01:19:42.453

7

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

edited Jun 15 '16 at 01:19

answered Jun 24 '15 at 15:51

grayob

325
3
8

score 6 · Answer 5 · edited Jan 23 '22 at 19:58

6

Use flatten, e.g. flatten(A1:B2). More details in this article.

If the 2d range is not in one piece, one can be created first with the ampersand or similar techniques. Afterwards flatten can be called on the resulting 2d range. The below example is a bit overkill but it is nice when working with dynamic 2d ranges, where the basic solution can't be easily used.

flatten(ARRAYFORMULA(SPLIT(ARRAYFORMULA(A1:A2&";"&C3:C4), ";")))

The article shows also how to easily unflatten a range using the, as well undocumented, skipping clause in a query.

edited Jan 23 '22 at 19:58

Andrew Marshall

95,083
20
220
214

answered Sep 27 '20 at 22:06

The Fool

16,715
5
52
86

Underappreciated answer. I was looking to have the rows merge in an alternating zip-kind-of fashion, and this did the trick super easily. Thanks! – Milo Wielondek Sep 29 '20 at 10:08
1

[deadneck](https://stackoverflow.com/users/14826917) posted an [Answer](https://stackoverflow.com/a/66535318) saying "I wanted to add this as a comment to The Fool's answer, but I don't have enough reputation."FLATTEN" is now documented.https://support.google.com/docs/answer/10307761" – Scratte Mar 09 '21 at 01:31

score 5 · Answer 6 · edited Feb 12 '15 at 02:51

5

Much more simple:

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

edited Feb 12 '15 at 02:51

Pang

9,564
146
81
122

answered Feb 11 '15 at 12:57

cyrilchampier

2,207
1
25
39

2

This formula also include all the blank cells in the column. – Rubén May 15 '15 at 03:22
1

This gives an error if the sheets have a different number of rows – Dobes Vandermeer Aug 21 '19 at 19:15

vstepaniuk · Answer 7 · 2020-01-22T10:46:02.453

1

=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

edited Jan 22 '20 at 10:46

answered Jan 22 '20 at 10:40

vstepaniuk

667
6
14

score -4 · Answer 8 · answered Feb 25 '12 at 09:43

-4

Try using your CONCATENATE argument with

=ArrayFormula(EXPAND(...))

answered Feb 25 '12 at 09:43

Guy

12,388
3
45
67

Stacking multiple columns on to one?

8 Answers8

Updated Answer

The following is the old deprecated answer

Linked

Related