How to union ranges in google spreadsheets

Question

I want to union ranges from any Google spreadsheets.

The example

Sheet1!A:A

{12, 131, 45}

Sheet2!A:A

{12, 131, 46}

The unknown function

=formula_for_union_range(Sheet1!A:A; Sheet2!:A:A)

should return

{12, 131, 45, 12, 131, 46}

The question

How is it possible?

The answer is in the [Google Sheets docs](https://support.google.com/docs/answer/6208276?hl=en-GB&ref_topic=9055396) — Lloyd, Jun 14 '22 at 11:05
@Lloyd, ten years ago, this article did not exist. Did I in a hurry to ask? — contributorpw, Jun 14 '22 at 18:40

score 166 · Answer 1 · answered Jun 06 '16 at 08:37

166

Just use:

={sheet1!a:a; sheet2!a:a}

answered Jun 06 '16 at 08:37

mik

3,575
3
20
29

3

This is the simplest answer. Amazingly, using a union this way even works with named ranges, ranges with more than one column, and a VLOOKUP formula. – ghoppe Nov 30 '16 at 22:38
1

In passing: You don't seem to be able to matrix syntax in named ranges. E.g. you cannot define a named range with disjoint cells. – Sherwood Botsford May 04 '19 at 15:17
1

Amazing. Works with named ranges too `={mon20190722T200000;mon20190729T200000;mon20190805T200000;mon20190812T200000}` – KCD Aug 15 '19 at 03:30
Just adding a note that if you're using `Query()`, this changes the column names to 'Col1', 'Col2' etc – user1034533 Apr 14 '22 at 03:33

Passant El.Agroudy · Answer 2 · 2017-04-13T16:11:20.377

105

You can merge them into 1 column then get the unique values. Check the following formula:

=UNIQUE({Sheet1!A:A;Sheet2!A:A})

edited Apr 13 '17 at 16:11

answered May 22 '15 at 22:59

Passant El.Agroudy

1,149
2
8
7

7

I'm not sure why this answer has fewer votes than the accepted one from Henrique. This seems to be right way to do it - with pure matrix notation - while the other answer seems to have completely unnecessary textual split/join commands which will slow things down a lot, and blow up if the involved cells have commas or semi-colons. – BeeOnRope Apr 06 '16 at 04:44
2

If you have multiple columns - why does it put next to each other? I used it to pull three columns from three sheets and it didn't join them just put them one next to the other? – Moseleyi Oct 20 '17 at 15:46
6

OP did not want unique values (`{12, 131, 45, 12, 131, 46}`) – mik Apr 16 '18 at 15:32
7

@Moseleyi: You probably did the same thing I did: use a comma instead of a semicolon. With comma, you get three columns. With semicolon, the ranges are unioned. – Reece Jan 21 '19 at 01:32
OP isn't asking for duplicate removal. – Sherwood Botsford May 04 '19 at 15:18
Google sheets removes semi-colons and replaces with commas! – rjurney Feb 08 '21 at 18:45
Duplicate removal is necessary. It is part of the definition of "union" – Jason Angel Apr 11 '23 at 18:12

contributorpw · Accepted Answer · 2021-06-07T03:45:54.540

Google Apps Script

And yet the question was about the script. I'm still successfully using the following code:

function unionRanges(e) {
  var result = [];
  var length = 0;
  var i = 0;
  try {
    for (i = 0; i < arguments.length; i++)
      length += arguments[i].length;
    if (length > 3000) return '#BIGRANGE';
    for (var i = 0; i < arguments.length; i++)
      result = result.concat(arguments[i].filter(function (el) {
        return el.join('').length > 0
      }));
    return result;
  } catch (err) {
    return JSON.stringify(err);
  }
}

Spreadsheets feature

But, as noted above, it is easier to use {}-notation.

={ Sheet1!A1:C10 ; Sheet2!A1:C34 }

Vertical concatenation

={ Range(Cols=N) ; Range(Cols=N) }

Horizontal concatenation

={ Range(Rows=M) , Range(Rows=M) }

It's possible to combine

={ { , , } ; { , , } }

Or something more hard

={{{;;},{;;}};{{;;},{;;}};{{;;},{;;}}}

Try something like this

={
   {{ 1; 2; 3},{ 4; 5; 6}};
   {{ 7; 8; 9},{10;11;12}};
   {{13;14;15},{16;17;18}}
 }

The internal horizontal concatenation is not required

={
    { 1; 2; 3},{ 4; 5; 6};
    { 7; 8; 9},{10;11;12};
    {13;14;15},{16;17;18}
}

Locale dependencies of argument delimiters

If your current locale supports , as an argument delimiter thnen you should use ; for a vertical concatenation and , for a horizontal concatenation.

Otherwise your argument delimiter is ; and you have to use ; and \ (without spaces), respectively.

Sheet `'Data 1'!A1:C20`

|   Name  |    Date   | Sum |
| Ethan   |  3/4/2017 |  31 |
| Logan   |  3/6/2017 |  62 |
| Brian   | 3/26/2017 |  61 |
|   ...   |     ...   | ... |

Sheet `'Data 2'!A1:C20`

|  Name   |    Date   | Sum |
| Nathan  | 3/30/2017 |  53 |
| Alyssa  | 3/13/2017 |  72 |
| John    | 3/24/2017 |  79 |
| Megan   | 3/16/2017 |  10 |
|   ...   |     ...   | ... |

Concatenation

Vertical concatenation

={'Data 1'!A1:C20;'Data 2'!A2:C20}

Result

|  Name  |    Date   | Sum |
| Ethan  |  3/4/2017 |  31 |
| Logan  |  3/6/2017 |  62 |
| Brian  | 3/26/2017 |  61 |
| ...    |       ... | ... |
| Nathan | 3/30/2017 |  53 |
| Alyssa | 3/13/2017 |  72 |
| John   | 3/24/2017 |  79 |
| ...    |       ... | ... |

Horizontal concatenation

={TRANSPOSE('Data 1'!A1:C20),TRANSPOSE('Data 2'!A2:C20)}

Result

| Name |   Ethan  |   Logan  |   Brian   | ... |   Nathan  |   Alyssa  |    John   |
| Date | 3/4/2017 | 3/6/2017 | 3/26/2017 | ... | 3/30/2017 | 3/13/2017 | 3/24/2017 |
| Sum  |       31 |       62 |        61 | ... |        53 |        72 |        79 |

More about this How to concatenate ranges in Google spreadsheets

Note, on vertical concatenation, the `;` are important; it doesn't work for `,` as it puts ranges horizontally without transposing them. — Arrcival, May 18 '21 at 14:48
Why is 3000 returns BIGRANGE ? What happens if we have 10k or 100k results? — Séb Cô, Apr 14 '23 at 15:56

Henrique G. Abreu · Answer 4 · 2014-08-11T11:59:40.617

21

Although a script can do this easily, I recommend using regular spreadsheet formulas, e.g.

=transpose(split(join(";";Sheet1!A:A)&";"&join(";";Sheet2!A:A);";"))

To remove duplicates, just wrap it in a unique formula:

=unique(transpose(...))

And to sort... =sort(...)

edited Aug 11 '14 at 11:59

answered May 29 '12 at 14:24

Henrique G. Abreu

17,406
3
56
65

3

This is weird. Joining cells into a big string just to split it up is not very efficient. See below for better answers. – Alan Draper Oct 12 '16 at 17:02
Yep, wrapping up the ranges in an array is a nice trick too. More importantly, it looks more clear. But worrying about formula performance in a spreadsheet is kinda funny. – Henrique G. Abreu Oct 31 '16 at 12:41

score 4 · Answer 5 · answered Jun 06 '21 at 21:43

At first when I tried ={Sheet1!A:A; Sheet2!A:A}, I thought it didn't work because I could only see results from the first sheet. Turned out it was including all the blank cells too!

To filter out blank and empty cells while preserving duplicates (unlike =UNIQUE) and without repeating yourself (unlike =FILTER()), you can use =QUERY(), like so:

=QUERY(
  {March!A1:Z; April!A2:Z; May!A2:Z}, 
  "select * where Col1 != '' and Col1 is not null", 
  0)

(Note that I am including the header row from the first sheet, and omitting it from the other sheets).

If your sheets don't contain cells with empty text, you can omit Col1 != '' and.

score 2 · Answer 6 · answered Jul 25 '18 at 21:49

2

Suppose you have:

   A    B   C   D   E   F
1: 1    2   3   4   5   6

It's possible to concatenate slices as either rows or columns.

For additional columns (same row), use a comma. ={$A1:$C1,$D1:$F1} yields:

1   2   3   4   5   6

For additional rows (same columns), use a semicolon. ={$A1:$C1;$D1:$F1} yields:

1   2   3
4   5   6

answered Jul 25 '18 at 21:49

jbryanscott

297
4
8

2

+1 for the tip on comma vs semicolon. Also if your comma separator is the comma character (Spain for instance), the union character for columns is \ instead of coma – mTorres Mar 27 '20 at 17:47

score 1 · Answer 7 · answered Jul 09 '18 at 04:10

1

If you want to union sheets and exclude rows with empty cells use the FILTER function in your formula:

=FILTER({Sheet1!A:A;Sheet2!A:A}, {Sheet1!A:A;Sheet2!A:A}<>"")

answered Jul 09 '18 at 04:10

mgig

2,395
4
21
36

How to union ranges in google spreadsheets

The example

The question

7 Answers7

Google Apps Script

Spreadsheets feature

Locale dependencies of argument delimiters

Sheet `'Data 1'!A1:C20`

Sheet `'Data 2'!A1:C20`

Concatenation

Vertical concatenation

Result

Horizontal concatenation

Result

Linked

How to union ranges in google spreadsheets

The example

The question

7 Answers7

Google Apps Script

Spreadsheets feature

Locale dependencies of argument delimiters

Sheet 'Data 1'!A1:C20

Sheet 'Data 2'!A1:C20

Concatenation

Vertical concatenation

Result

Horizontal concatenation

Result

Linked

Sheet `'Data 1'!A1:C20`

Sheet `'Data 2'!A1:C20`