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 |
Key |
Aardvark |
Koala |
Zebra |
Key |
Aaron |
Abdullah |
Alice |
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 |