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
-
1That's not possible with plain old formula's. Have a look at this answer: webapps.stackexchange.com/a/39292/29140 – Jacob Jan Tuinstra Jun 4 '13 at 12:27
-
1Related: How to zip two columns together in a Google Spreadsheet – batpigandme Jun 4 '13 at 12:49
-
@JacobJanTuinstra Unfortunately that merges sheets, not columns. – Undistraction Jun 4 '13 at 12:53
-
It works for column ranges as well. – Jacob Jan Tuinstra Jun 4 '13 at 13:02
-
1Update: See this answer, the accepted one is for the old version of Sheets. – user79865 Mar 6 '16 at 16:53
|
Show 2 more comments
The following formula can be used to merge one column after another:
={A1:A5; M1:M5}
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
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),";")))
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
answered Jun 4 '13 at 13:09
-
-
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
