I need to combine multiple columns into a single column, so:

``````one     four    seven
two     five    eight
three   six     nine
``````

Becomes:

``````one
two
three
four
five
six
seven
eight
nine
``````
• That's not possible with plain old formula's. Have a look at this answer: webapps.stackexchange.com/a/39292/29140 Jun 4, 2013 at 12:27
• Jun 4, 2013 at 12:49
• @JacobJanTuinstra Unfortunately that merges sheets, not columns. Jun 4, 2013 at 12:53
• It works for column ranges as well. Jun 4, 2013 at 13:02
• Update: See this answer, the accepted one is for the old version of Sheets.
– user79865
Mar 6, 2016 at 16:53

The following formula can be used to merge one column after another:

``````={A1:A5; M1:M5}
``````
• This is not good if you have a lot of blank cells. Jan 19, 2017 at 10:03
• I needed to solve the blank cells issue and got this to work: `=Filter(({C2:C;E2:E;G2:G;I2:I}),({C2:C;E2:E;G2:G;I2:I})<>"")`
– Lod
Dec 27, 2021 at 20:02

If the columns are A, E, G (for example), then the following command combines them:

``````={filter(A:A, len(A:A)); filter(E:E, len(E:E)); filter(G:G, len(G:G))}
``````

Here, I first filter the columns by `len()` to remove blank cells. Then I combine the columns together.

An example combining columns in different sheets:

``````={filter(sheetone!A:A, len(sheetone!A:A)); filter(sheettwo!A:A, len(sheettwo!A:A))}
``````

• e.g. merge(and sort|uniq) 3 columns excluding first header row `=UNIQUE(SORT({filter(B2:B, len(B2:B)); filter(C2:C, len(C2:C)); filter(D2:D, len(D2:D))}))` Mar 21, 2018 at 15:05
• and e.g. re-combine columns showing side-by-side comparison if original columns(B,C,D) contain item in the merged column `=IF(ISERROR(MATCH(\$F2,B\$2:B,0)),"",\$F2)` Mar 21, 2018 at 15:09

You can try this function to combine two columns

``````=unique(transpose(split(join(";",A:A)&join(";",B:B),";")))
``````

Install the `VMERGE` script from the script gallery:

The script you just installed, makes it possible to combine multiple ranges into one: `=VMERGE(range1,range2, ect)`

See example file I've prepared for you to play with: VMERGE and Ranges

• Beautiful. Much obliged. Jun 4, 2013 at 13:57
• At this time the Script Gallery is available only for old spreadsheets that had not being migrated yet. May 3, 2015 at 0:44
• Yeah; no more VMERGE still. Jul 18, 2015 at 9:13