17

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
25

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. – orschiro Jan 19 '17 at 10:03
8

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

Adapted from my answer from Stack Overflow.

  • 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))})) – gaoithe Mar 21 '18 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) – gaoithe Mar 21 '18 at 15:09
0

In the new Google Spreadsheets, the script gallery has been replaced with the add-on store.

You can try this function to combine two columns

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

Install the VMERGE script from the script gallery:

screen shot

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. – Undistraction Jun 4 '13 at 13:57
  • At this time the Script Gallery is available only for old spreadsheets that had not being migrated yet. – Rubén May 3 '15 at 0:44
  • 2
    Yeah; no more VMERGE still. – Jason C Jul 18 '15 at 9:13

Not the answer you're looking for? Browse other questions tagged or ask your own question.