Join or union ranges from multiple tabs in Google Sheets

A spreadsheet is the wrong tool for everything, but it’s also a very available tool. No results came up when I was searching for how to join data sets in different ranges in a Google Sheets or Excel worksheet, so here’s how you do it.

Suppose you have these three data sets in a Google Sheets worksheet:

Key
Aardvark
Aardwolf
Aaron
Sheet 1
Key
Aardvark
Koala
Zebra
Sheet 2
Key
Aaron
Abdullah
Alice
Sheet 3

How do you join them and list the unique elements on a different sheet? You want something along the lines of this formula:

=index(sort(unique({sheet1!A$2:A$4; sheet2!A$2:A$24; sheet3!A$2:A$4; })),row()-1)

{range1; range2} joins the contents of multiple ranges into a union

unique eliminates duplicates, sort() is self explanatory, and index(range, i) gets an element

The output should be something like this:

Key
Aardvark
Aardwolf
Aaron
Abdullah
Alice
Koala
Zebra
Summary

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.