CODEWITHSUNDEEP
google-sheets
abritez
abritez

Reputation: 2626

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

Sheet2

FinalSheet

Upvotes: 54

Views: 112039

Answers (8)

The Fool
The Fool

Reputation: 20467

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.

Upvotes: 6

vstepaniuk
vstepaniuk

Reputation: 868

=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

Upvotes: 1

grayob
grayob

Reputation: 335

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

Upvotes: 8

PowerAktar
PowerAktar

Reputation: 2428

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

Upvotes: 16

Lake
Lake

Reputation: 1155

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.

Upvotes: 86

Diego Contezini
Diego Contezini

Reputation: 109

You can use this:

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

Works perfect here!

Upvotes: 10

cyrilchampier
cyrilchampier

Reputation: 2248

Much more simple:

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

Upvotes: 5

Guy
Guy

Reputation: 12901

Try using your CONCATENATE argument with

=ArrayFormula(EXPAND(...))

Upvotes: -4

Related Questions